Many-to-Many Relationships: Junction Tables Done Properly

A doctor has several specialties and a specialty has several doctors. Neither table can hold the link, because a column holds one value and the link has many on both sides. The answer is a third table with one row per pair, the junction table, and the 2024 series drew it in its sixth part. This part builds it, reads it back both ways, and shows the one constraint that separates a junction table from a pile of pairs.

The junction table

Section 2 creates doctors and specialties, each with its own identity, and section 3 creates the link:

CREATE TABLE doctor_specialties (
    doctor_id     bigint NOT NULL
        CONSTRAINT fk_doctor_specialties_doctors REFERENCES doctors (id),
    specialty_id  bigint NOT NULL
        CONSTRAINT fk_doctor_specialties_specialties REFERENCES specialties (id),
    since         date NOT NULL,
    CONSTRAINT pk_doctor_specialties PRIMARY KEY (doctor_id, specialty_id)
);
-- The primary key's index starts with doctor_id ("a doctor's specialties").
-- The other direction ("doctors with a specialty") gets its own index.
CREATE INDEX ix_doctor_specialties_specialty_id ON doctor_specialties (specialty_id, doctor_id);

Three decisions are in those lines. The primary key is the pair, so the same doctor cannot hold the same specialty twice; this is the composite natural key that Part 2 said is right for exactly one kind of table. The column since lives here because it belongs to the pair: the date Dr. Rossi took up paediatrics is not a fact about her alone nor about paediatrics alone. And the second index exists because the primary key's index is ordered by doctor_id first, which answers "this doctor's specialties" but not "the doctors with this specialty"; the reversed index answers that one. The SQL Server script is identical apart from Part 1's spellings.

Section 4 loads three doctors, four specialties and five pairs:

INSERT INTO doctor_specialties (doctor_id, specialty_id, since) VALUES
    (1, 1, '2015-03-01'),   -- Rossi:  General Practice
    (1, 2, '2019-09-01'),   -- Rossi:  Paediatrics
    (2, 1, '2018-01-15'),   -- Mensah: General Practice
    (2, 3, '2021-06-01'),   -- Mensah: Cardiology
    (3, 2, '2020-02-01');   -- Weber:  Paediatrics

Reading it back, both ways

A many-to-many is read through two joins, and each direction starts from a different table. Section 5a starts from the specialty: the doctors who do paediatrics are Dr. Elena Rossi since 2019 and Dr. Hannah Weber since 2020. Section 5b starts from the doctor and folds the specialties into one line each:

SELECT d.full_name,
       string_agg(s.name, ', ' ORDER BY s.name) AS specialties
FROM doctors AS d
JOIN doctor_specialties AS ds ON ds.doctor_id = d.id
JOIN specialties AS s ON s.id = ds.specialty_id
GROUP BY d.id, d.full_name
ORDER BY d.full_name;

    full_name     |          specialties
------------------+-------------------------------
 Dr. Elena Rossi  | General Practice, Paediatrics
 Dr. Hannah Weber | Paediatrics
 Dr. Kwame Mensah | Cardiology, General Practice

SQL Server has the same function with a different place for the ordering: STRING_AGG(s.name, N', ') WITHIN GROUP (ORDER BY s.name). The results are identical. Section 5c counts doctors per specialty and keeps the specialty nobody has yet, which needs a LEFT JOIN and a COUNT of the junction column rather than COUNT(*):

       name       | doctors
------------------+---------
 Cardiology       |       1
 Dermatology      |       0
 General Practice |       2
 Paediatrics      |       2

The pair twice

Section 6 inserts Dr. Mensah's cardiology a second time:

Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint 'pk_doctor_specialties'. Cannot insert duplicate key in object 'dbo.doctor_specialties'. The duplicate key value is (2, 3).
The statement has been terminated.

ERROR:  duplicate key value violates unique constraint "pk_doctor_specialties"
DETAIL:  Key (doctor_id, specialty_id)=(2, 3) already exists.

That refusal is the whole reason the pair is the key. The rest of the part is about keeping it when the key changes shape.

A surrogate id on the junction

Sometimes the junction row needs to be pointed at: a certificate that belongs to one doctor-specialty pair, a rota slot, a tool that wants every table to have a single-column key. Then the junction gets its own id, and the pair moves from the primary key to a unique constraint:

CREATE TABLE doctor_specialties_v2 (
    id            bigint GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_doctor_specialties_v2 PRIMARY KEY,
    doctor_id     bigint NOT NULL
        CONSTRAINT fk_doctor_specialties_v2_doctors REFERENCES doctors (id),
    specialty_id  bigint NOT NULL
        CONSTRAINT fk_doctor_specialties_v2_specialties REFERENCES specialties (id),
    since         date NOT NULL,
    CONSTRAINT uq_doctor_specialties_v2_pair UNIQUE (doctor_id, specialty_id)
);

The five pairs copied across get ids 1 to 5, and the duplicate is refused just as before, this time by uq_doctor_specialties_v2_pair. Section 7b is the table people actually build when they add a surrogate id: the same columns with no unique pair. The duplicate goes in without a word, and the report from section 5b now reads:

    full_name     |               specialties
------------------+------------------------------------------
 Dr. Elena Rossi  | General Practice, Paediatrics
 Dr. Hannah Weber | Paediatrics
 Dr. Kwame Mensah | Cardiology, Cardiology, General Practice

Every count is wrong from that row on, and nothing will ever say so. Whichever shape you choose, the pair is unique; the surrogate id is an addition, not a replacement.

A plain pair

Not every junction carries a payload. Section 8 links patients to allergies with nothing but the two ids, and the composite key is all the table needs:

CREATE TABLE patient_allergies (
    patient_id  bigint NOT NULL CONSTRAINT fk_patient_allergies_patients REFERENCES patients (id),
    allergy_id  bigint NOT NULL CONSTRAINT fk_patient_allergies_allergies REFERENCES allergies (id),
    CONSTRAINT pk_patient_allergies PRIMARY KEY (patient_id, allergy_id)
);

Section 8a lists every patient with their allergies, and a COALESCE turns the empty aggregate of a patient with none into the words "none recorded", which is what a screen should show rather than a blank:

    full_name    |      allergies
-----------------+---------------------
 Maria Garcia    | Penicillin
 David Chen      | none recorded
 Aisha Khan      | Peanuts, Penicillin
 Fatima Al-Sayed | Latex

Section 8b asks the question that matters before a prescription, who is allergic to penicillin, and gets Aisha Khan and Maria Garcia. Part 7 builds the table this question protects, the visit with its prescribed medicines, which is a junction with a payload of its own.

Frequently asked

How do I model a many-to-many relationship in SQL?

With a junction table: one row per pair, two foreign keys, and the pair as the primary key so the same link cannot be stored twice. Attributes that belong to the pair, such as the date a doctor took up a specialty, live in the junction table too.

Should a junction table have its own id column?

Only when other tables need to reference one link or a tool insists on single-column keys. Then add the identity id and keep the pair as a UNIQUE constraint. A surrogate id without the unique pair lets duplicates in silently and doubles every report.

How do I list many-to-many values as one comma-separated line per row?

Join through the junction table, group by the parent, and aggregate the names: string_agg(name, ', ' ORDER BY name) in PostgreSQL, STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) in SQL Server. Use a LEFT JOIN and COALESCE to keep parents that have no links.

Next: Part 5, choosing column types, where a sum of ten tenths is not one and a local time is not a moment.