Associative entity

<h2 id="using-associative-tables">Using associative tables</h2>
<p>An example of the practical use of an associative table would be to assign permissions to users. There can be multiple users, and each user can be assigned zero or more permissions.  Individual permissions may be granted to one or more users. 
</p>
CREATE TABLE Users (
    UserLogin varchar(50) PRIMARY KEY,
    UserPassword varchar(50) NOT NULL,
    UserName varchar(50) NOT NULL
);

CREATE TABLE Permissions (
    PermissionKey varchar(50) PRIMARY KEY,
    PermissionDescription varchar(500) NOT NULL
);

-- This is the junction table.
CREATE TABLE UserPermissions (
    UserLogin varchar(50) REFERENCES Users (UserLogin),
    PermissionKey varchar(50) REFERENCES Permissions (PermissionKey),
    PRIMARY KEY (UserLogin, PermissionKey)
);

<p>A <a href="/facts/Select_(SQL)/cYnKkmFo">SELECT</a>-statement on a junction table usually involves <a href="/facts/Join_(SQL)/Cxl5lxcR">joining</a> the main table with the junction table:
</p>
SELECT * FROM Users
JOIN UserPermissions USING (UserLogin);

<p>This will return a list of all users and their permissions.
</p><p><a href="/facts/Insert_(SQL)/UvooNUmg">Inserting</a> into a junction table involves multiple steps: first inserting into the main table(s), then updating the junction table.
</p>
-- Creating a new User
INSERT INTO Users (UserLogin, UserPassword, UserName)
VALUES ('SomeUser', 'SecretPassword', 'UserName');

-- Creating a new Permission
INSERT INTO Permissions (PermissionKey, PermissionDescription)
VALUES ('TheKey', 'A key used for several permissions');

-- Finally, updating the junction
INSERT INTO UserPermissions (UserLogin, PermissionKey)
VALUES ('SomeUser', 'TheKey');

<p>Using foreign keys, the database will automatically dereference the values of the UserPermissions table to their own tables.
</p>
<h2 id="see-also">See also</h2>
<ul><li><a href="/facts/Many-to-many_(data_model)/2MtUxMVt">Many-to-many (data model)</a></li>
<li><a href="/facts/Relational_database/pEKGmJLy">Relational database</a></li>
<li><a href="/facts/Unique_key/NKGBxUHR">Unique key</a></li></ul>

<ul><li>Hoffer, Jeffrey A.; Prescott, Mary B.; McFadden, Fred R. (2004). <i>Modern Database Management</i> (7th ed.). Prentice Hall. <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 978-0131453203.</li>
<li><a href="/facts/Edgar_F._Codd/QCSbPYkK">Codd, E. F.</a> (1970). <a href="https://doi.org/10.1145%2F362384.362685">"A Relational Model of Data for Large Shared Data Banks"</a>. <i>Communications of the ACM</i>. 13 (6). ACM: 377–387. <a href="/facts/Doi_(identifier)/muM9Etpq">doi</a>:<a href="https://doi.org/10.1145%2F362384.362685">10.1145/362384.362685</a>.</li></ul>

Associative entity open-in-new

Associative entity