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
  8. Companion script p03b-sqlserver.sql or p03b-postgresql.sql: one child table per rule, SET DEFAULT, and RESTRICT against a deferred NO ACTION

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.

CASCADE, SET NULL, SET DEFAULT, NO ACTION and RESTRICT, side by side

The three rules above are the ones a clinic needs. Both engines know five, and the names are easy to confuse, so the companion script builds one parent table, doctors, one child table per rule, and deletes one doctor per table. Run on SQL Server 2022 LocalDB 16.0.1210.3 and PostgreSQL 18.4, this is what each rule did to the child rows:

RuleThe child rows when the parent is deletedSQL ServerPostgreSQL
CASCADEDeleted with it. The delete reports 1 row on both engines, although three schedule rows went too.YesYes
SET NULLKept; the column becomes NULL. The column must be nullable.YesYes
SET DEFAULTKept; the column takes its DEFAULT. Two appointments moved to doctor 1, the placeholder row "On-call (unassigned)".YesYes
NO ACTIONThe delete is refused: Msg 547, SQLSTATE 23503. The default on both engines.YesYes
RESTRICTThe delete is refused: SQLSTATE 23001, "violates RESTRICT setting".No, Msg 156Yes

SET DEFAULT needs a default that points at a row that exists; the script keeps a placeholder doctor for it. What happens when the default row is missing was not tested. PostgreSQL's refusal under RESTRICT reads differently from the one under NO ACTION, and carries a different code: 

ERROR:  update or delete on table "doctors" violates RESTRICT setting of foreign key constraint "fk_sick_notes_doctors" on table "sick_notes"
DETAIL:  Key (id)=(6) is referenced from table "sick_notes".

Code that catches 23503 only will miss a RESTRICT refusal. So what is the difference between the two? Only when the check runs. NO ACTION is checked at the end of the statement, and PostgreSQL can defer it to COMMIT; RESTRICT is checked at once and cannot be deferred. Section 5 of the companion script declares a foreign key ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED, deletes a doctor and re-inserts the same id before the commit, and the transaction goes through: 

DELETE FROM doctors WHERE id = 7;
DELETE 1
INSERT INTO doctors (id, full_name) OVERRIDING SYSTEM VALUE VALUES (7, 'Dr. Nadia Petrova');
INSERT 0 1
COMMIT;
COMMIT

Without the re-insert the same transaction fails at COMMIT with the 23503 text from section 4. The same DEFERRABLE on a RESTRICT key is accepted without a word, and the DELETE fails at once; the re-insert then gets "current transaction is aborted", and COMMIT prints ROLLBACK. SQL Server rejects DEFERRABLE outright, Msg 102, Incorrect syntax near 'DEFERRABLE': on SQL Server, NO ACTION is the only refusal there is, and it always runs at the end of 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 is the difference between ON DELETE RESTRICT and NO ACTION?
Both refuse to delete a parent row that children still reference. NO ACTION is checked at the end of the statement, and PostgreSQL can defer it to COMMIT, so a transaction may delete the parent and re-insert it before the check runs. RESTRICT is checked immediately and cannot be deferred. PostgreSQL reports them with different codes, 23503 for NO ACTION and 23001 for RESTRICT. SQL Server has no RESTRICT keyword; NO ACTION is its only refusal, and SET DEFAULT is its other option besides CASCADE and SET NULL.

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.