sql · level 2 · unit 7 · lesson 1
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
A pairs rows from two tables, one pair per row.
A student can join many clubs, and a club has many students. Neither table can hold the other's id, because one box holds only one value. So you build a link table in the middle.
Each row of memberships pairs one student id with one club id, and both are foreign keys. PRIMARY KEY (student_id, club_id) is a : each pair can appear only once.
To read it, join through the middle: memberships to students, and memberships to clubs.
SELECT s.name, c.club
FROM memberships m
JOIN students s ON m.student_id = s.id
JOIN clubs c ON m.club_id = c.id
ORDER BY s.name, c.club;A composite key goes on its own line at the end of CREATE TABLE, after the columns.
guess first
Student 1 joins club 1 again. What happens?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
CREATE TABLE memberships (
student_id INTEGER,
club_id INTEGER,
PRIMARY KEY (student_id, club_id)
);
INSERT INTO memberships VALUES (1, 1), (1, 3), (2, 1);
INSERT INTO memberships VALUES (1, 1);
SELECT * FROM memberships;your turn
Your turn. Who goes to a club?
Type a club between the quotes: Chess, Choir or Robotics. You get every student in it, through the link table.
result
Your answer appears here as a table.
fix it
Student 1 cannot join a second club. Fix it!
result
Your answer appears here as a table.
- Read the error. Which column must be unique?
- With the key on
student_idalone, each student can appear once, so only in one club. - Change PRIMARY KEY (student_id) to PRIMARY KEY (student_id, club_id), then press run.
nailed it
you designed a many-to-many link. :D
- A link table pairs ids from two tables.
- A composite key lets each pair appear only once.
- Read it by joining through the middle table.
new words in your collection
- link table
- composite key
This lesson isn’t saved. A free account remembers every lesson you finish.