A foreign key’s ON DELETE action should follow from one question: what does the child row mean once its parent is gone? If it means nothing, CASCADE deletes it with the parent. If it is still a valid row with the link removed, SET NULL keeps it. If it is a record someone relies on, RESTRICT refuses the delete and makes you decide.

SET NULL needs the most care, because PostgreSQL accepts it in cases where it can never succeed and only says so when someone deletes a parent row in production. Every statement below was run on PostgreSQL 18.3 in a throwaway container, with a support desk schema: agents as the parent and tickets as the child, and for the timing tests 10,000 users and 2,000,000 tickets.

ON DELETE SET NULL vs CASCADE: which should you use?

Decide per foreign key, from the child’s point of view:

SET NULL fits when NULL has an honest meaning in that column, such as “nobody is assigned”. It is the wrong choice when NULL would erase something the business needs, such as who handled a ticket that later goes to an audit. There, keep the parent row, mark it inactive, and let RESTRICT stop the delete.

RESTRICT and NO ACTION both refuse a delete that would leave orphans. NO ACTION is the default, and the PostgreSQL documentation on constraints gives the one difference: RESTRICT “does not allow the check to be deferred until later in the transaction”. On a key declared DEFERRABLE INITIALLY DEFERRED, or deferred with SET CONSTRAINTS ALL DEFERRED, NO ACTION lets the same transaction insert a replacement parent or delete the dangling children before the check runs at commit. DEFERRABLE alone is not enough: the check is still immediate, and the delete fails on the spot.

Three ways SET NULL is accepted and then fails

PostgreSQL checks that the referenced columns exist and are unique when you create the key. It does not check that the child column can hold NULL. The failure arrives with the first delete of a parent that has children.

The column is NOT NULL. This key is created without complaint:

CREATE TABLE tickets (
    id bigint PRIMARY KEY,
    assignee_id bigint NOT NULL REFERENCES agents (id) ON DELETE SET NULL
);

Deleting an agent who has a ticket then fails with ERROR: null value in column "assignee_id" of relation "tickets" violates not-null constraint, and the agent stays. The other engines refuse the definition instead, with one catch on MySQL. MySQL 8.4 parses an inline REFERENCES like the one above and ignores it, so that CREATE TABLE succeeds with no foreign key at all. Written as a table-level FOREIGN KEY (assignee_id) REFERENCES agents (id) ON DELETE SET NULL, it is rejected with ERROR 1830 (HY000): Column 'assignee_id' cannot be NOT NULL: needed in a foreign key constraint 'tickets_ibfk_1' SET NULL. SQL Server 2022 rejects it with Msg 1761: Cannot create the foreign key ... with the SET NULL referential action, because one or more referencing columns are not nullable.

A check constraint needs the value. A ticket in the assigned status must have an assignee:

CREATE TABLE tickets (
    id bigint PRIMARY KEY,
    status text NOT NULL,
    assignee_id bigint REFERENCES agents (id) ON DELETE SET NULL,
    CHECK (status <> 'assigned' OR assignee_id IS NOT NULL)
);

The column is nullable, so the key looks fine, but deleting the agent of an assigned ticket fails with new row for relation "tickets" violates check constraint "tickets_check". SET NULL writes a new version of the child row, and every check on that row runs again. The fix is a decision rather than a syntax change: either the check allows it, or the application reassigns the tickets before the agent goes.

The key is composite. In a multi-tenant schema the key usually includes the tenant, so a ticket cannot point at another tenant’s agent:

CREATE TABLE tickets (
    tenant_id bigint NOT NULL,
    id bigint,
    assignee_id bigint,
    PRIMARY KEY (tenant_id, id),
    FOREIGN KEY (tenant_id, assignee_id) REFERENCES agents (tenant_id, id) ON DELETE SET NULL
);

Plain SET NULL sets every column of the key, so the delete fails with null value in column "tenant_id" of relation "tickets" violates not-null constraint. Since PostgreSQL 15, whose release notes describe the change as letting ON DELETE SET actions “affect only specified columns”, you can name the column:

FOREIGN KEY (tenant_id, assignee_id) REFERENCES agents (tenant_id, id) ON DELETE SET NULL (assignee_id)

With that key the same delete succeeds, and the ticket keeps tenant_id = 7 with assignee_id empty.

What SET NULL does besides writing NULL

SET NULL is an UPDATE of every child row, not a quiet unlink. A BEFORE UPDATE trigger on tickets that stamps updated_at fired when the agent was deleted and moved the ticket’s updated_at to the time of the delete, so a “recently changed” list will show every ticket the agent had. Each updated row is a new row version, with the same vacuum work as any other update.

SET DEFAULT has its own trap: the default has to exist in the parent. With assignee_id bigint DEFAULT 0 and no agent 0, deleting an agent failed with insert or update on table "tickets" violates foreign key constraint, because the fallback row the design assumed was never inserted.

Does ON DELETE need an index on the child column?

For correctness, no. For speed, yes, and it matters for SET NULL, CASCADE and RESTRICT alike, because each has to find the children of the deleted parent. PostgreSQL does not create that index for you. With 2,000,000 tickets across 10,000 users:

Without the index, each deleted user costs one full scan of tickets, 158 MB with its primary key, so a cleanup job that deletes 1,000 users scans the table 1,000 times however few tickets they had.

Changing the action later is not an ALTER. ALTER TABLE ... ALTER CONSTRAINT does not accept ON DELETE, so the key is dropped and added again, and adding it checks every existing row. On the 2,000,000-row table that re-check took 300 ms while holding back writes to both tickets and users. On a large table, drop the old key and add the new one NOT VALID in the same transaction, so there is no moment without a key (0.9 ms), then run VALIDATE CONSTRAINT separately (243 ms here), which lets writes carry on.

How Schemity handles ON DELETE SET NULL

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. When you design the next one, the delete behaviour is part of the relationship: ON DELETE and ON UPDATE are set in the relation dialog, and choosing SET NULL there makes the foreign key’s columns nullable in the same edit and draws the relationship as optional. A primary key column cannot become nullable, so it is left alone.

The lint rules cover what the dialog cannot fix on its own:

• SET NULL on a column that is NOT NULL. Reported for a key read from a database or imported from SQL, on every engine: PostgreSQL accepts the key and fails the delete, MySQL and SQL Server refuse the key.
• Foreign key has no index on its columns. Reported for tickets.assignee_id above, since every parent delete scans the child without it.

Changing the action on a connected diagram plans the drop and the re-add, and impact analysis reports it before anything runs: “Drops and recreates a foreign key on tickets”, and “Checking the new key holds back writes to tickets while it reads ~200K rows, 16 MB on disk”. The finding names the child table only; on PostgreSQL the referenced table waits too. The planned migration is the plain drop and add, run in one transaction, not the NOT VALID version, so on a large table write that one yourself.

The current release stores the action without a column list, so a PostgreSQL 15 key such as ON DELETE SET NULL (assignee_id) reads back as plain SET NULL, and lint reports its tenant_id column as NOT NULL. For that key, keep the column-list definition in your migration file.

Which ON DELETE action to choose

• The child has no meaning without the parent: CASCADE, and index the foreign key.
• The child stays valid without the link: SET NULL, on a nullable column, with no check that requires the value, and with a column list if the key includes a tenant column.
• The child is a record people rely on: RESTRICT or NO ACTION, and deactivate the parent instead of deleting it.
• A known fallback row exists: SET DEFAULT, after inserting that row.

The reach of a cascade across several tables, and why MySQL does not log it, is covered in ON DELETE CASCADE is invisible in your ERD. Whether to declare the foreign key at all is the question in should you use foreign key constraints, and the way a composite key with a NULL column stops being checked is in unique constraints and nullable columns.