ON DELETE CASCADE and friends: what really happens when you delete a row

October 21, 20267 min read

Every foreign key you draw is secretly a policy decision. Not "can this column reference that table" - that part is obvious - but what should the database do when the referenced row goes away? SQL answers that with five clauses: CASCADE, SET NULL, RESTRICT, NO ACTION and SET DEFAULT. Most schemas pick one without thinking, and the choice quietly decides whether deleting a user wipes their entire order history or the database refuses to let you.

One schema, three outcomes:

The same users-orders foreign key drawn three times: CASCADE deletes the order rows, SET NULL empties the user_id column, RESTRICT and NO ACTION reject the delete with a foreign key error

First, the direction - because this is where everyone trips

The most upvoted confusion about ON DELETE CASCADE isn't what it does, it's where to write it and which side it acts on. So, once, slowly:

The clause is written on the foreign key column - the child side (orders.user_id here).
The word DELETE refers to the parent row being deleted.
The action is applied to the child rows that referenced it.

So user_id bigint REFERENCES users(id) ON DELETE CASCADE in the orders table reads: "when a user is deleted, delete their orders." It never means "deleting an order deletes the user" - the arrow only points one way, down the dependency.

The setup

Same pair as the identifying-relationship post, because it's the perfect example:

CREATE TABLE orders (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users(id)
            ON DELETE CASCADE,   -- ← the decision lives here
  total   numeric(10,2) NOT NULL
);

DELETE FROM users WHERE id = 42;

Swap the ON DELETE clause and the exact same DELETE statement has three completely different results.

The three you'll actually use

CASCADE - the referencing rows are deleted too. Order 7 and order 8 cease to exist. This is the right answer only when the child is a part of the parent - the identifying case: order lines, invoice lines, a profile row that belongs to its account. The test from that post applies directly: would the child row make sense if you forgot its parent? If not, cascade is honest - the rows are meaningless without it.

SET NULL - the child rows survive, but user_id becomes NULL. The orders keep their totals and dates; they just belong to no one. Two requirements: the column must be nullable (the database will refuse SET NULL on a NOT NULL column), and the link must be genuinely optional in your domain - "this shipment has no carrier assigned yet" is optional; "this order has no user" is usually a data bug.

RESTRICT - the database rejects the DELETE outright. The user row stays, the error above is what your application sees, and cleanup becomes an explicit decision: archive the orders, reassign them, or confirm the deletion on purpose. For any table you'd cry about losing - orders, payments, invoices - this is the honest choice. The data dies only when a human insists.

The two you'll meet but rarely choose

NO ACTION - the default. Write no clause at all and you get this, and it looks identical to RESTRICT: the delete still fails. The difference is timing, and it only matters in Postgres: RESTRICT checks immediately and cannot be deferred; NO ACTION can wait until the end of the transaction. That window lets you do things like re-parent rows mid-transaction - delete the old parent, point the children somewhere new, commit clean. (In MySQL the two behave identically.)

SET DEFAULT - the child rows survive with the FK set to its declared default value. It only works if a default exists and a row with that value does too, or the delete fails anyway. Genuinely rare - it earns its obscurity.

The trap: cascade chains multiply

Here's the mistake that turns CASCADE from a convenience into an incident report. Suppose you add cascade in two places:

-- orders.user_id   REFERENCES users(id)      ON DELETE CASCADE
-- order_items.order_id REFERENCES orders(id) ON DELETE CASCADE

Now DELETE FROM users WHERE id = 42 doesn't delete two orders - it deletes the user, every order they ever made, every line item inside those orders, and anything cascading further down. One statement, thousands of rows, no confirmation dialog. The chain follows your diagram's structure exactly, which is why the schema review habit worth adopting is: walk every line in the diagram and say its delete story out loud - "deleting this entity takes what with it?" If any answer makes you flinch, the clause is wrong, not the review.

The rule of thumb that keeps this safe: cascade only where the child would be meaningless without the parent - exactly the identifying relationships. Everything else gets RESTRICT (or the NO ACTION default) so deletion requires a human decision, in the right order, on purpose.

Side by side

Clause	Child rows on parent delete	Column must be nullable	Use when
`CASCADE`	deleted too	no	child is a part-of detail (order lines)
`SET NULL`	kept, FK emptied	yes	the link is truly optional
`RESTRICT`	delete rejected	no	child is a record worth keeping
`NO ACTION` (default)	delete rejected (deferrable in Postgres)	no	same as RESTRICT; need mid-transaction re-parenting
`SET DEFAULT`	kept, FK set to default	no	rare - needs a valid default row

Decide it deliberately, once per line

None of the five is "correct" - a payments schema and a cache table want opposite answers. What's wrong is not choosing: writing a bare REFERENCES and letting the default decide your data-loss policy by accident. The cheap discipline is reviewing the delete story of every line on the diagram before it becomes DDL.

And when you're sketching in FigJam, FigSchema keeps every relationship anchored to its exact columns, so when you walk the canvas asking "what does deleting this take with it?", each line points at the precise foreign key that will enforce the answer. Free, and offline.

Frequently asked questions

Does ON DELETE CASCADE delete rows in the child table or the parent table?

The clause lives on the child's foreign key column, fires when a parent row is deleted, and deletes the referencing child rows. orders.user_id REFERENCES users(id) ON DELETE CASCADE means "deleting a user deletes their orders" - never the reverse. Deleting a child row never touches the parent.

What is the difference between RESTRICT and NO ACTION?

Both reject a delete that would orphan child rows. The difference is when the check runs: RESTRICT is immediate and cannot be deferred; NO ACTION (the SQL default) may wait until the end of the transaction, which allows re-parenting children mid-transaction in Postgres. In MySQL and SQL Server the two behave the same.

Is ON DELETE CASCADE a good idea?

For part-of children - order lines, invoice lines, a profile row tied to its account - yes: the rows are meaningless without the parent, and cascade enforces that. For records with independent value (orders, payments, audit entries) it's dangerous: an accidental parent delete silently wipes history, and cascades chain through your whole diagram. Those tables want RESTRICT.

What happens if I don't specify ON DELETE at all?

You get NO ACTION - the delete of a referenced parent fails with a foreign key violation. It's the safe default: nothing is ever lost automatically, and deletion becomes an explicit, ordered operation. If you want different behavior, say so in the DDL rather than relying on the default.