Many-to-Many Relationships and Junction Tables

Objectives

By the end of this lesson, you should be able to:

• Explain what a many-to-many relationship is
• Explain why it can’t be implemented with a single foreign key
• Build a junction table to implement one

💡 Why this matters: A student can enroll in many courses, and a course can have many students enrolled. Neither side is “the one,” neither students nor courses can hold a single foreign key pointing at the other, this shape needs a table of its own.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

Why One Foreign Key Isn’t Enough

A one-to-many relationship works by putting a foreign key on the “many” side (employees.department_id, from two lessons ago). But many-to-many has no “many” side to put it on, both sides can relate to many rows on the other. A single student_id column on courses, or a single course_id column on students, could only ever record one match, not many.

The Junction Table

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(id),
    course_id INTEGER REFERENCES courses(id),
    enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id)
);

INSERT INTO students (name) VALUES ('Maya Fischer'), ('Sam Whitfield');
INSERT INTO courses (title) VALUES ('Databases 101'), ('Intro to SQL');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1), (1, 2), (2, 1);

enrollments is a junction table (also called a join table or associative table): a table whose entire purpose is recording which students row is connected to which courses row, one row per connection. Both student_id and course_id are foreign keys, each pointing back to its own table, this is the mechanism that actually implements many-to-many, two one-to-many relationships meeting in the middle.

SELECT s.name, c.title
FROM enrollments e
JOIN students s ON e.student_id = s.id
JOIN courses c ON e.course_id = c.id
ORDER BY s.name, c.title;

     name       |     title
-----------------+----------------
 Maya Fischer   | Databases 101
 Maya Fischer   | Intro to SQL
 Sam Whitfield  | Databases 101

Maya is enrolled in both courses, Sam in one, Databases 101 has two students enrolled. Every combination is just a row in enrollments, no limit on how many rows either students or courses participates in.

PRIMARY KEY (student_id, course_id)

PRIMARY KEY (student_id, course_id) is a composite primary key, covered in full in the next lesson, a primary key made of two columns together rather than one. Here, it guarantees a student can’t enroll in the same course twice:

INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);

ERROR:  duplicate key value violates unique constraint "enrollments_pkey"

Maya (student_id = 1) is already enrolled in Databases 101 (course_id = 1), so this exact pairing is rejected, even though neither student_id nor course_id alone is unique in this table.

Try It

1. Create actors and movies tables, then a junction table movie_casts connecting them, with a composite primary key.
2. Insert 2 actors and 2 movies, then insert enrollments so one actor appears in both movies.
3. Write a query joining all three tables to list which actor appears in which movie (a preview of Module 10’s JOIN syntax).
4. Explain, in your own words, why a many-to-many relationship can’t be modeled with a single foreign key on either students or courses alone.

Recap

• A many-to-many relationship means either side can relate to many rows on the other side.
• It can’t be modeled with a single foreign key on either table, both sides would need to hold multiple values.
• A junction table implements many-to-many with two foreign keys, one per side, one row per connection, often with a composite primary key across both foreign key columns.

Next lesson: composite primary keys in more depth, primary keys made of more than one column.