ON DELETE CASCADE vs SET NULL: let the column decide

The ON DELETE CASCADE vs SET NULL question usually gets answered one foreign key at a time, by whoever writes the migration, on the day they write it. That works until a delete has to touch ten tables written by several people over months. On a compliance platform I work on, users could not delete a piece of evidence once AI validation had looked at it. The delete code cleared the two tables that referenced evidence when it was written. Four more had arrived since. The database refused the delete, and the operator saw "service temporarily unavailable, please try again later". This post covers the rule I now use to decide, the test that enforces it, and the cases where neither answer fits.

Why the delete broke months after it was written

Nothing in the delete was wrong on the day it shipped. It listed every table that referenced evidence, and it cleared them. The problem was that the list lived in one function, and new tables with an evidence_id column were added elsewhere without anyone updating it.

The second problem was the error. A foreign key violation is a permanent refusal. Retrying will never make it succeed. Reporting it as a temporary outage sent the operator to retry something that could not work, and told nobody what was actually in the way.

ON DELETE CASCADE vs SET NULL: let nullability decide

The rule became mechanical, and it reads straight off the column:

• A NOT NULL evidence_id means the row exists only to join evidence to something. Without the evidence it means nothing, so it is deleted with the evidence. That is cascade.
• A nullable evidence_id means the row is a record in its own right that happened to cite evidence. The record stays and the reference is detached. That is set null.

The Postgres documentation on foreign keys makes the same distinction in its own words: cascade fits when the referencing row is a component of the referenced one and cannot exist independently. Nullability is how the schema already says which of those a table is. In SQL, the two shapes look like this (illustrative names):

CREATE TABLE control_evidence (
  control_id  bigint NOT NULL REFERENCES controls (id) ON DELETE CASCADE,
  evidence_id bigint NOT NULL REFERENCES evidence (id) ON DELETE CASCADE,
  PRIMARY KEY (control_id, evidence_id)
);

ALTER TABLE treatment_actions
  ADD COLUMN evidence_id bigint REFERENCES evidence (id) ON DELETE SET NULL;

Whether you express it as foreign key actions or as two lists in the delete code, the decision is the same, and nobody has to argue about it per table.

A test that fails when a new column is not classified

A rule only holds if something checks it. The platform's delete works from two lists, one for tables to delete from and one for references to detach, and a test now fails when any table gains an evidence_id column that neither list names. A sketch of the idea:

DELETE_WITH_EVIDENCE = {"control_evidence", "validation_results"}
DETACH_FROM_EVIDENCE = {"treatment_actions", "risk_notes"}

def test_every_evidence_reference_is_classified(conn):
    rows = conn.execute(text(
        "SELECT table_name, is_nullable FROM information_schema.columns "
        "WHERE table_schema = 'public' AND column_name = 'evidence_id'"
    )).all()
    for table, nullable in rows:
        expected = DETACH_FROM_EVIDENCE if nullable == "YES" else DELETE_WITH_EVIDENCE
        assert table in expected, f"{table}.evidence_id is not classified"

That guard found two more tables the audit had missed. Days later, a schema contract check caught a new evidence_id column on treatment actions, which joined the detach list: deleting the evidence drops the citation and leaves the record of work standing. This is the same idea as declaring every index in code so the schema and the code cannot drift, which I wrote about in TypeORM synchronize dropped my indexes, and it pairs well with a schema audit like the one in how I run a Postgres index audit on a production snapshot.

The audit also found one table carrying an evidence_id with no foreign key at all. Deleting evidence would have silently orphaned a request's file. And I accepted one known trade-off: deleting evidence now takes its validation history with it, because those rows only exist to describe that evidence.

What is the difference between ON DELETE CASCADE and SET NULL?

ON DELETE CASCADE deletes the referencing rows when the referenced row is deleted. ON DELETE SET NULL keeps the referencing rows and sets their foreign key column to null. Use cascade for rows that only make sense with their parent, such as join rows, and set null for records that should outlive what they point to.

When the answer is to refuse the delete

Some deletes should not happen at all, and for those the right foreign key behavior is RESTRICT or the default NO ACTION, plus a clear message.

• Deleting evidence that a live risk rests on is refused, and the refusal names the risks. The caller supersedes the evidence instead of deleting it.
• Deleting a risk that had been decided on used to fail on a foreign key and surface as a 503 telling the caller to retry. It is now an explicit refusal that names cancel or close as the alternative.
• Deleting a linked risk now returns a 409 that names what is still attached.

409 Conflict is the right status for this. RFC 9110 defines it as a conflict with the current state of the target resource, used where the user might be able to resolve it, and says the response should carry enough information to recognize the source of the conflict. That is exactly what "these three risks still use this evidence" does.

When a record should keep a name instead of a reference

The last group is records that must keep pointing at something after it is gone. A foreign key cannot do that, by design.

• Score traces. A control score's trace was a list of evidence ids, and 166 of 230 pointed at evidence that no longer existed, so the trace explained nothing. The evidence name is now stored beside the id at scoring time. Old rows are read through a property instead of a backfill.
• Links across record kinds. Links between seven kinds of record share one table, and the target id deliberately has no foreign key. The register must keep showing that a risk was linked to something since deleted, so the label is stored with the link.
• Remediation evidence. Evidence is stamped with the action it proves, deliberately not as a foreign key, so deleting the action leaves the artefact an auditor reads.

My rule here: if a record is history, copy the human-readable part at write time. History that depends on a live join stops being history the day the other row goes. If the deletes themselves get large, the batching approach in deleting old rows in batches without locking still applies.

Where I'd start

1. List every column that references the table you delete from, with its nullability.
2. Classify each: NOT NULL means delete with the parent, nullable means detach.
3. Add a test that fails when a new referencing column is not classified.
4. Pick the references that should block a delete, and return a 409 that names them.
5. For history and audit records, store a name beside the id instead of relying on the join.