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)

“Eddie Felson: Church of the Good Hustler.
Charlie: Looks more like a morgue to me. Those tables are the slabs they lay the stiffs on.
Eddie Felson: I’ll be alive when I get out, Charlie.”
—Sydney Carroll, U.S. screenwriter, and Robert Rossen. Eddie Felson (Paul Newman)