# ON DELETE SET NULL vs CASCADE vs RESTRICT in PostgreSQL: Which to Use

> Pick the action by what the child row means once its parent is gone: `CASCADE` when it means nothing, `SET NULL` when it stays valid with the link removed, and `RESTRICT` or `NO ACTION` when it is a record that must not change behind anyone's back. `SET NULL` is the one PostgreSQL accepts and then refuses at delete time, if the column is `NOT NULL`, a check needs the value, or the key is composite and nulls a tenant column along with it.

Source: https://schemity.com/blog/postgres-on-delete-set-null-vs-cascade/

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:

| After the parent row is deleted, the child row is | Action | Example |
| --- | --- | --- |
| Meaningless | `CASCADE` | Order lines of an order, sessions of a user |
| Still valid, with the link removed | `SET NULL` | A ticket whose assignee left, a post whose editor's account was deleted |
| A record that must not change silently | `RESTRICT` or `NO ACTION` | An invoice for a customer, a payment for an order |
| Still valid, pointing at a known fallback row | `SET DEFAULT` | Tickets moved to an "unassigned" queue row |

`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:

```sql
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:

```sql
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:

```sql
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](https://www.postgresql.org/docs/release/15.0/) describe the change as letting `ON DELETE SET` actions "affect only specified columns", you can name the column:

```sql
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:

| Delete | No index on `assignee_id` | With an index |
| --- | --- | --- |
| One user | 71 ms | 1.8 ms |
| 100 users in one statement | 7,489 ms | 98 ms |

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](https://schemity.com/doc/relationships/): `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.

![Schemity's canvas after switching the tickets to agents relation to ON DELETE SET NULL, not yet migrated: tickets.assignee_id now carries the N marker for a nullable column, the relation line is drawn optional at the agents end, and the Lint drawer shows one finding, Foreign key has no index on its columns, with the SET NULL on a column that is NOT NULL finding gone](https://schemity.com/images/blog/postgres-on-delete-set-null-vs-cascade/relation-set-null-nullable.webp)

The [lint rules](https://schemity.com/doc/schema-lint/) 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.

![Schemity's Lint drawer for a PostgreSQL 18.3 diagram with tickets and agents: under Fails at runtime, tickets.assignee_id on tickets_assignee_id_fkey reads SET NULL on a column that is NOT NULL, with the detail ON DELETE SET NULL would write NULL into tickets.assignee_id, which is NOT NULL. The constraint is accepted, and the statement on agents fails when it runs; under Costs, the same column reads Foreign key has no index on its columns, with the detail that every delete or key update in agents scans tickets; the assignee_id row is marked on the canvas](https://schemity.com/images/blog/postgres-on-delete-set-null-vs-cascade/lint-set-null-not-null.webp)

Changing the action on a connected diagram plans the drop and the re-add, and [impact analysis](https://schemity.com/schema-migration-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.

![Schemity's Migration confirm dialog for changing ON DELETE on tickets_assignee_id_fkey to NO ACTION: under Holds back other statements, Checking the new key holds back writes to tickets while it reads ~200K rows, 16 MB on disk; under Spreads through the diagram, Drops and recreates a foreign key on tickets; and the SQL ALTER TABLE "tickets" DROP CONSTRAINT "tickets_assignee_id_fkey" followed by ALTER TABLE "tickets" ADD CONSTRAINT "tickets_assignee_id_fkey" FOREIGN KEY ("assignee_id") REFERENCES "agents" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION](https://schemity.com/images/blog/postgres-on-delete-set-null-vs-cascade/impact-fk-recreate.webp)

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](https://schemity.com/blog/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](https://schemity.com/blog/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](https://schemity.com/blog/unique-constraints-and-nullable-columns/).

## Frequently asked questions

### Should I use `ON DELETE SET NULL` or `CASCADE`?

Use `CASCADE` when the child row has no meaning without its parent, such as an order line or a session. Use `SET NULL` when the child row is still a valid record with the link removed, such as a ticket whose assignee left, and make the column nullable. When the child is a record people rely on, such as an invoice, use `RESTRICT` and deactivate the parent instead of deleting it.

### Why does `ON DELETE SET NULL` fail in PostgreSQL?

Because PostgreSQL accepts `SET NULL` on a `NOT NULL` column and only refuses the `NULL` when a parent row is deleted, with `null value in column ... violates not-null constraint`. The same happens when a check constraint requires the column, and on a composite key that also sets a `NOT NULL` tenant column to `NULL`. SQL Server 2022, and MySQL 8.4 with a table-level `FOREIGN KEY` clause, refuse the constraint when it is created instead.

### Does `ON DELETE SET NULL` need an index?

It needs one for speed, not for correctness. Every parent delete looks up the child rows by the foreign key column, so without an index on it PostgreSQL scans the child table. On 2,000,000 tickets, deleting 100 users took 7.5 seconds without an index on `assignee_id` and 98 ms with one.