Delete a customer and their orders, their order lines, their addresses, and their payment methods all disappear automatically. No application code to write, no orphans left behind, nothing forgotten.
That is genuinely useful. It also means a single DELETE statement against one row can remove millions of rows across a dozen tables, take minutes, and hold locks the whole time — and nothing in the statement suggests it will.
What changed in 2026
- Retention requirements complicated cascades. Rules about what must be kept made blanket deletion less appropriate for some child data.
- Soft delete interactions became clearer. Cascades do not follow soft-delete flags, which produces inconsistency — see soft delete patterns.
- The missing-index problem stayed common. Unindexed child foreign keys remained a frequent cause of slow deletes.
- Restrict-by-default gained ground. More teams chose explicit deletion over automatic cascading.
The referential actions
| Action |
On parent delete |
Suits |
NO ACTION / RESTRICT |
Error if children exist |
Default; forces explicitness |
CASCADE |
Delete children too |
Genuinely dependent data |
SET NULL |
Keep children, clear the link |
Optional relationships |
SET DEFAULT |
Keep children, set a default |
Rare |
The question that decides which: does the child have meaning without the parent?
An order line has no meaning without its order — cascade is right. An order has considerable meaning without the customer record, particularly for accounting and legal retention — cascade is wrong, and SET NULL or restrict is better.
A comment on a deleted post is arguable and depends on the product.
Getting this wrong in the cascade direction is worse than getting it wrong in the restrict direction. Restrict produces an error you notice; cascade produces silent data loss you discover later.
The index requirement
The performance trap. When a parent row is deleted, the database must find the children referencing it. That lookup uses an index on the child's foreign key column — and databases do not create that index automatically in most engines.
Without it, every parent deletion scans the entire child table. Deleting one customer scans the orders table. Deleting a hundred scans it a hundred times.
This is one of the most common causes of a delete that is inexplicably slow, and the fix is a single index on the referencing column. It is worth auditing: list foreign keys without a supporting index on the child side, and add them.
The cascade also acquires locks on every affected row across every affected table, which on a deep cascade means a long-running transaction holding many locks — with the knock-on effects described in MVCC explained.
Depth and blast radius
Cascades chain. Customer cascades to orders, orders cascade to lines, lines cascade to allocations. A single statement traverses the whole graph.
Nothing in the statement indicates this. DELETE FROM customers WHERE id = 5 looks like it deletes one row.
Two disciplines help. Know the depth — map which relationships cascade before relying on them, ideally as part of schema review. And delete explicitly for large operations — issuing the child deletes yourself, in batches, gives you control over transaction size and progress visibility that a single cascading statement does not.
For bulk cleanup, explicit batched deletion is almost always better than a cascade, because you can bound each transaction and observe progress.
Common mistakes
- Cascade where the child has independent value. Silent loss of data you needed.
- No index on the child foreign key. Every cascade scans.
- Bulk deletes relying on cascade. One enormous transaction.
- Not knowing the cascade depth. Blast radius larger than expected.
- Mixing cascade with soft delete. Cascades ignore the flag, producing inconsistency.
- Cascade on audit or history tables. Defeats their purpose entirely.
- Assuming restrict is the default everywhere. Check; engines differ.
FAQ
Should I use cascade at all?
For genuinely dependent data with bounded child counts, it is clean and safe. For anything with independent value, or unbounded child counts, prefer explicit deletion.
How do I find missing foreign key indexes?
Query the catalog for foreign key constraints and check whether a matching index exists on the referencing side. Worth running as a periodic audit — most schemas have a few.
What about cascading updates?
Same mechanism for changed keys, and generally a sign the key should be immutable. Changing a primary key is rarely something to design for.
Does cascade work with soft delete?
No, and this is a real inconsistency. Soft-deleting a parent leaves children untouched, since no actual delete occurred — you must handle it in application logic, per soft delete patterns.
Where to go next
For the soft-delete interaction, read soft delete patterns. For the indexes cascades require, database indexing explained, and for the long-transaction effects of large deletes, MVCC explained.