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)

    “The evening falters. Couples in their coats
    Are leaving gaps already, and the rest
    Move tables closer.”
    —Philip Larkin (1922–1986)