Junction Tables for Many-to-Many

A single foreign key cannot model many-to-many

Every real schema hits this. One student takes many courses, and each course holds many students. One song belongs to many playlists, and each playlist holds many songs. You cannot store that with a foreign key, because an FK is a single column holding a single value: it points one child row at one parent, so it models one-to-many and nothing more. To let both sides be "many," you add a third table whose entire job is to hold the pairs.

The mental model: two one-to-many relationships pointing into a bridge

Schema

• student_id
• name

• student_id
• course_id
• enrolled_at

• course_id
• title

• enrollmentsn-1students— one row per (student, …)
• enrollmentsn-1courses— … course) pair

A junction table (also called an associative or bridge table) has one row per related (A, B) pair. Read it as two one-to-many relationships aimed inward: each parent owns many junction rows, and each junction row points at exactly one row in each parent. Its shape is stereotyped:

• Two FK columns, one to each parent.
• A composite primary key over those two FKs. It does double duty: it identifies the pair, and it blocks the same pair from being stored twice.
• Optional relationship attributes: facts that belong to the pairing, not to either entity alone.

An attribute belongs on the junction when it needs both keys to be meaningful. grade and enrolled_at depend on a specific (student, course) pair, so they cannot live on students (a student has many grades) or on courses. The test is always "does this fact require both entities together?"

Worked example

CREATE TABLE enrollments (
    student_id  INTEGER REFERENCES students(student_id),
    course_id   INTEGER REFERENCES courses(course_id),
    enrolled_at TEXT,
    PRIMARY KEY (student_id, course_id)   -- one row per (student, course)
);

INSERT INTO enrollments VALUES (1, 10, '2026-01-15');
INSERT INTO enrollments VALUES (1, 11, '2026-01-15');
INSERT OR IGNORE INTO enrollments VALUES (1, 10, '2026-02-01');  -- pair already exists

SELECT COUNT(*) FROM enrollments;  -- 2, not 3: the composite PK made the repeat a no-op

INSERT OR IGNORE is the clean way to prove the guard: a duplicate pair violates the primary key, and OR IGNORE skips that row silently instead of raising an error. You can layer more guards the same way. In the playlist exercise, a second constraint UNIQUE (playlist_id, position) stops two songs from claiming the same slot, which is separate from the PK that stops one song appearing twice in a playlist.

Pitfall: drop the composite PK and everything double-counts

Without the composite PK, nothing stops (1, 10) from being inserted twice, and every COUNT and SUM over enrollments silently inflates. A subtler trap on SQLite specifically: a PRIMARY KEY column still accepts NULL (a long-standing engine quirk), and SQLite treats NULLs as distinct, so (NULL, 10) can be stored repeatedly. Declare the FK columns NOT NULL when a partial pair should never exist.

Interview nuance: a composite primary key (student_id, course_id) builds one index sorted by student_id first, then course_id. It answers "which courses does student 1 take?" with a fast seek, but "which students are in course 10?" filters on the non-leading column and falls back to a scan. This is the leftmost-prefix rule, and it is why a many-to-many queried in both directions usually needs a second index on (course_id, student_id).