A foreign key does two things. It refuses a child row that points nowhere, which Part 1 showed, and it decides what happens to the children when the parent goes away, which is this part. Three one-to-many links, three different ON DELETE rules, and one thing neither engine does for you: index the foreign key column.

Steps in this part
  1. Download p03-sqlserver.sql or p03-postgresql.sql and run it as in Part 1
  2. Section 2: find the three foreign keys and the rule each one carries
  3. Section 4: deleting Maria Garcia is refused by name; deleting Yuki Tanaka goes through
  4. Section 5: run the catalogue query that finds a foreign key from the name in an error
  5. Section 6: deleting appointment 3 takes its queue entry with it
  6. Section 7: closing Room 2 leaves two appointments with an empty room_id
  7. Section 8: list the indexes before and after, and notice which ones the foreign keys did not create

Three links, three rules

Section 2 builds patients, rooms, appointments and queue entries. Each link gets the rule that fits what the rows mean: 

CREATE TABLE appointments (
    id          bigint GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_appointments PRIMARY KEY,
    patient_id  bigint NOT NULL
        CONSTRAINT fk_appointments_patients REFERENCES patients (id),
    room_id     bigint
        CONSTRAINT fk_appointments_rooms REFERENCES rooms (id) ON DELETE SET NULL,
    starts_at   timestamptz NOT NULL,
    status      varchar(20) NOT NULL
);
CREATE TABLE queue_entries (
    id              bigint GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_queue_entries PRIMARY KEY,
    appointment_id  bigint NOT NULL
        CONSTRAINT fk_queue_entries_appointments REFERENCES appointments (id) ON DELETE CASCADE,
    checked_in_at   timestamptz NOT NULL,
    called_at       timestamptz,
    CONSTRAINT uq_queue_entries_appointment_id UNIQUE (appointment_id)
);

An appointment belongs to a patient, and a patient with history should not vanish by accident, so that link keeps the default, NO ACTION: the delete is refused. An appointment happens in a room, but the room is incidental; if Room 2 closes, the appointment survives with no room, so room_id is nullable and the rule is SET NULL. A queue entry is nothing without its appointment, so it follows it: CASCADE. The SQL Server script is the same apart from the spellings from Part 1. PostgreSQL also accepts ON DELETE RESTRICT, which behaves like NO ACTION for everything in this part; SQL Server rejects the word with Msg 156, Incorrect syntax near the keyword 'RESTRICT'.

Section 3 loads four patients, two rooms, four appointments and two queue entries:

 id |  full_name   |  room  |       starts_at        |  status   |     checked_in_at
----+--------------+--------+------------------------+-----------+------------------------
  1 | Maria Garcia | Room 1 | 2026-09-28 09:00:00+00 | CheckedIn | 2026-09-28 08:52:00+00
  2 | Maria Garcia | Room 2 | 2026-10-05 09:00:00+00 | Booked    |
  3 | David Chen   | Room 1 | 2026-09-28 09:30:00+00 | CheckedIn | 2026-09-28 09:21:00+00
  4 | Tomás Silva  | Room 2 | 2026-09-28 10:00:00+00 | Booked    |
(4 rows)

NO ACTION: the delete is refused

Section 4 deletes Maria Garcia, who has two appointments. SQL Server:

Msg 547, Level 16, State 1, Line 3
The DELETE statement conflicted with the REFERENCE constraint "fk_appointments_patients". The conflict occurred in database "dd_p03", table "dbo.appointments", column 'patient_id'.
The statement has been terminated.

PostgreSQL:

ERROR:  update or delete on table "patients" violates foreign key constraint "fk_appointments_patients" on table "appointments"
DETAIL:  Key (id)=(1) is still referenced from table "appointments".

Yuki Tanaka, who has no appointments, is deleted without complaint. Both messages name the constraint and the child table; PostgreSQL also names the parent key that was blocked. Section 5 shows how to go from that name to the definition, because the day you meet this error the constraint will be one of dozens. The standard view exists on both engines with the same query text: 

SELECT constraint_name, delete_rule, update_rule
FROM information_schema.referential_constraints
ORDER BY constraint_name;

        constraint_name        | delete_rule | update_rule
-------------------------------+-------------+-------------
 fk_appointments_patients      | NO ACTION   | NO ACTION
 fk_appointments_rooms         | SET NULL    | NO ACTION
 fk_queue_entries_appointments | CASCADE     | NO ACTION

Section 5b makes the case for naming again. A foreign key created without a name gets one from the engine, and a delete then fails against "FK__visit_not__appoi__440B1D61" on SQL Server, a truncated table name, a truncated column name and a hex number that differs in every database the script runs in. PostgreSQL's generated visit_notes_appointment_id_fkey is at least readable. A name you chose is readable everywhere.

CASCADE and SET NULL

Section 6 deletes appointment 3. Before and after, the queue:

 id | appointment_id |     checked_in_at
----+----------------+------------------------
  1 |              1 | 2026-09-28 08:52:00+00
  2 |              3 | 2026-09-28 09:21:00+00
(2 rows)

DELETE FROM appointments WHERE id = 3;

 id | appointment_id |     checked_in_at
----+----------------+------------------------
  1 |              1 | 2026-09-28 08:52:00+00
(1 row)

The entry for appointment 3 is gone, and psql's DELETE 1 counted only the appointment; cascaded rows are never counted, on either engine, which is worth remembering when a delete reports one row and removes a thousand. Section 7 closes Room 2: 

DELETE FROM rooms WHERE id = 2;

 id | patient_id | room_id |       starts_at
----+------------+---------+------------------------
  1 |          1 |       1 | 2026-09-28 09:00:00+00
  2 |          1 |         | 2026-10-05 09:00:00+00
  4 |          3 |         | 2026-09-28 10:00:00+00
(3 rows)

Appointments 2 and 4 kept their rows and lost their room. SET NULL needs a nullable column: SQL Server refuses to create such a foreign key on a NOT NULL column (Msg 1761), while PostgreSQL accepts the constraint and fails later, at the first delete, with a not-null violation. Design the column and the rule together.

The index nobody made

Every query that joins appointments to a patient, and every cascade or refusal check, looks up patient_id. Section 8 lists the indexes after all this work: 

 Schema |              Name               | Type  | Owner |     Table
--------+---------------------------------+-------+-------+---------------
 public | pk_appointments                 | index | dd    | appointments
 public | pk_patients                     | index | dd    | patients
 public | pk_queue_entries                | index | dd    | queue_entries
 public | pk_rooms                        | index | dd    | rooms
 public | uq_patients_phone               | index | dd    | patients
 public | uq_queue_entries_appointment_id | index | dd    | queue_entries

Primary keys and unique constraints made indexes. The three foreign keys made none. On both engines a foreign key is a rule, not an index, so every check of it on a large table is a scan until you add one: 

CREATE INDEX ix_appointments_patient_id ON appointments (patient_id);
CREATE INDEX ix_appointments_room_id    ON appointments (room_id);

The exception in the listing proves the rule: queue_entries.appointment_id is indexed, but only because its unique constraint made the index. ClinicLive has ix_appointments_patient_id because EF Core creates an index for every foreign key by convention. The tool did it; the database would not have. If you write DDL by hand, the index is your job, and Part 9 measures what it is worth.

Deleting a patient anyway, and the SQL Server limit

Section 9 does what NO ACTION asks: delete the children first. Removing Maria's appointments cascades away her queue entry, and then her row goes, leaving 2 patients, 1 appointment and 0 queue entries. That is more typing than a cascade, and it is the point: deleting a patient's history should be a decision, not a side effect.

Section 10 is the limit SQL Server readers hit when they decide to cascade everything. Notes that point at an appointment and at the patient give the patient two cascade paths into the same table, and SQL Server refuses the second one: 

Msg 1785, Level 16, State 1, Line 1
Introducing FOREIGN KEY constraint 'fk_c_notes_c_patients' on table 'c_notes' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 1, Line 1
Could not create constraint or index. See previous errors.

PostgreSQL accepts both paths and deleting the patient empties both tables. SQL Server's own advice works: make the second path NO ACTION, and the delete still removes the note through the appointment, because the check runs after the statement.

What ClinicLive chose: every foreign key in the app is ON DELETE CASCADE, the EF Core default for a required relationship, so deleting a patient silently deletes appointments, queue entries and device registrations. For a demo clinic that is convenient. For a real one, the patient link should be NO ACTION and the delete a deliberate, logged operation, which Part 10 turns into a soft delete.

Frequently asked

Should I use ON DELETE CASCADE?
Only when the child row means nothing without its parent, such as a queue entry without its appointment. For a parent with history, such as a patient, keep NO ACTION so the delete is refused and has to be a deliberate operation. Use SET NULL when the child should survive and simply forget the parent.
Does a foreign key create an index automatically?
No, on neither SQL Server nor PostgreSQL. A foreign key is a rule. Add an index on the foreign key column yourself, or check that your ORM did: EF Core creates one for every foreign key by convention.
What does 'may cause cycles or multiple cascade paths' mean in SQL Server?
A table can be reached by two cascading foreign key chains from the same parent, and SQL Server refuses to create the second one. Make one of the paths ON DELETE NO ACTION; the delete still reaches the row through the other path. PostgreSQL allows multiple cascade paths.

Next: Part 4, many-to-many, where a doctor has several specialties and a specialty several doctors. The 2024 series drew these links in Finding relationships between entities.