A many-to-many relationship is one where each side can link to many of the other: a student takes many courses, and each course has many students; an order contains many products, and each product appears on many orders. SQL tables cannot express that directly, so you model it with a third table that sits between the two. It is one of the most common patterns in database design, and getting it right early saves a lot of pain later.
Why you can't do it with a foreign key
A plain foreign key handles one-to-many. An order belongs to one customer, so orders carries a customer_id. That works because each order has exactly one customer.
It falls apart for many-to-many. You cannot put a course_id on the students table, because a student has many courses, and you cannot put a student_id on courses for the same reason. Cramming a list of IDs into a single column breaks first normal form and makes the data impossible to query or index properly. The relationship needs its own home.
The junction table
That home is a junction table (also called an associative, join, or link table). It holds a foreign key to each side, and each row represents one link.
CREATE TABLE students (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(120) NOT NULL
);
CREATE TABLE courses (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL
);
CREATE TABLE enrollments (
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);Two things make this correct:
- The composite primary key
(student_id, course_id)guarantees the same student cannot be enrolled in the same course twice. - The two foreign keys keep the data honest: you cannot enroll a student or course that does not exist, and deleting one can be made to clean up its links.
Storing data about the relationship
The junction table is not just a pair of IDs. Any fact that describes the link itself, rather than either side alone, belongs here. When did the student enrol? What is their role on a project? That data depends on both sides at once, so it has nowhere else to live:
CREATE TABLE enrollments (
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
enrolled_at DATE NOT NULL,
grade CHAR(2),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);enrolled_at and grade are facts about this particular enrolment, not about the student or the course in general. This is exactly where they should sit.
Reading across it
To list a student's courses, you JOIN through the junction table:
SELECT c.title, e.enrolled_at
FROM enrollments e
JOIN courses c ON c.id = e.course_id
WHERE e.student_id = 42;Index the foreign keys so these lookups stay fast. The composite primary key already indexes (student_id, course_id); add an index on (course_id, student_id) too if you often query from the course side, so "who is in this course" is just as quick.
A common mistake to avoid
Do not give the junction table a single surrogate id and then forget to enforce uniqueness on the pair. If you prefer a surrogate key, that is fine, but keep a UNIQUE (student_id, course_id) constraint, or you will end up with duplicate links and no way to tell which is real. This pattern shows up throughout the database design examples guide, from order lines to social-media follows.
Frequently asked questions
How do you represent a many-to-many relationship in SQL?
With a third table, called a junction or associative table, that holds a foreign key to each of the two related tables. Each row links one record on each side, so a student can have many courses and a course can have many students, all through rows in the junction table.
What should the primary key of a junction table be?
Usually a composite primary key made of the two foreign keys together, for example (student_id, course_id). That enforces uniqueness so the same pair cannot be linked twice. Some teams add a separate surrogate id as well, which is fine, but keep a unique constraint on the pair.
Can a junction table hold extra columns?
Yes, and that is one of its strengths. Data that describes the relationship itself, such as the date a student enrolled or their role on a project, belongs on the junction table, because it depends on both sides at once rather than on either one alone.
What is the difference between a junction table and a join?
They are different things with similar names. A junction table is a permanent table in your schema that stores the relationship. A JOIN is a query operation that combines rows from tables at read time. You use a JOIN to read across a junction table.
Modelling relationships that are getting tangled, or reviewing a schema before it locks in? That is database consulting work. The full database design examples guide walks through more real schemas.