Junction Table - Using Junction Tables

Using Junction Tables

A SELECT-statement on a junction table usually involves joining the main table with the junction table:

SELECT * FROM Users JOIN UserPermissions USING (UserLogin);

This will return a list of all users and their permissions.

Inserting into a junction table involves two steps: first inserting into the main table (for example, a new User), then updating the junction table.

-- 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');

Using foreign keys, the database will automatically dereference the values of the UserPermissions table to their own table.

Read more about this topic:  Junction Table

Famous quotes containing the words junction and/or tables:

    “In order to get to East Russet you take the Vermont Central as far as Twitchell’s Falls and change there for Torpid River Junction, where a spur line takes you right into Gormley. At Gormley you are met by a buckboard which takes you back to Torpid River Junction again.”
    —Robert Benchley (1889–1945)

    “O these encounterers, so glib of tongue,
    That give a coasting welcome ere it comes,
    And wide unclasp the tables of their thoughts
    To every ticklish reader! Set them down
    For sluttish spoils of opportunity
    And daughters of the game.”
    —William Shakespeare (1564–1616)