Build1 publisher2 min readPublished
PostgreSQL accepts SET NULL foreign keys that fail only when a parent row is deleted
PostgreSQL 18.3 accepts three kinds of ON DELETE SET NULL key at creation and rejects them only when a parent with children is deleted. A Schemity post that tested each case says to pick every key's action by what the child row means once its parent is gone.
The Engineer · Build desk
What happened
- In a multi-tenant schema, plain SET NULL on a composite (tenant_id, assignee_id) key nulls every key column, so the delete fails on the NOT NULL tenant_id.
- MySQL 8.4 refuses the table-level form of the NOT NULL key with ERROR 1830, and SQL Server 2022 refuses it with Msg 1761, both when the table is defined.
- Written inline, the same key is parsed and ignored by MySQL 8.4, so its CREATE TABLE succeeds with no foreign key at all.
- RESTRICT and NO ACTION both refuse a delete that would leave orphaned child rows, and NO ACTION is what PostgreSQL uses by default.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- exposure Migrations and tests that only create the schema pass against these keys, so the first signal can be a failed delete in production with the parent row still in place.
- decision Where NULL would erase who handled a ticket that later goes to audit, the post's answer is to keep the agent row, mark it inactive, and let RESTRICT stop the delete.
- cost Fixing the check-constraint case costs a product decision: either the check is loosened to allow the null, or the application reassigns tickets before an agent is removed.
- constraint Declaring a key DEFERRABLE by itself does not help a delete, because the check stays immediate and the statement fails on the spot.
At creation, PostgreSQL validates only the referenced side of a foreign key. The referenced columns must exist and must be unique [5]. It does not check whether the referencing column can hold NULL, so the error waits for the first delete of a parent that has children [5]. The simplest case declares `assignee_id bigint NOT NULL REFERENCES agents (id) ON DELETE SET NULL` on a tickets table, and the table is created without complaint [6]. Deleting an agent who has a ticket then fails with `null value in column "assignee_id" of relation "tickets" violates not-null constraint`, and the agent row stays [6].
The check-constraint case comes from what SET NULL does to the child. It writes a new version of the row, and every check on that row runs again [10]. Put `CHECK (status <> 'assigned' OR assignee_id IS NOT NULL)` on a nullable assignee_id and the key looks fine. Deleting the agent of an assigned ticket still fails on `tickets_check` [9].
Schemity's blog published the post. Its author builds that desktop ERD tool and uses it for the examples [1]. The testing is careful. Every statement ran on PostgreSQL 18.3 in a throwaway container [4]. The schema is a support desk, with agents as the parent and tickets as the child [4]. The post prints each engine's exact error text [6][8]. These are engine behaviours, so I'd expect them to hold for any schema with the same constraint shape on 18.3. The post does not show other releases [4].
According to the post, SET NULL fits a column where NULL has an honest meaning, such as "nobody is assigned" [13]. I think that is the right question to ask of every key, and PostgreSQL will not ask it at creation [5]. In my view, a SET NULL key on a NOT NULL column, under a check that reads the column, or inside a composite key with a tenant column should fail review [3].
For records that must survive, the choice between RESTRICT and NO ACTION comes down to timing. The PostgreSQL documentation, as the post quotes it, says RESTRICT "does not allow the check to be deferred until later in the transaction" [18]. On a key declared `DEFERRABLE INITIALLY DEFERRED`, or after `SET CONSTRAINTS ALL DEFERRED`, NO ACTION lets one transaction insert a replacement parent or delete the dangling children before the check runs at commit [16].
What to watch
- Whether a future PostgreSQL release rejects SET NULL on a NOT NULL child column at CREATE time, as MySQL 8.4 and SQL Server 2022 already do for the table-level form.
- Whether the three failure shapes reproduce on PostgreSQL releases older than 18.3, the only version the post tested.