Lorixen.
EN

ON DELETE CASCADE, RESTRICT or SET NULL: choosing safely

Short answer: A foreign key's ON DELETE rule decides what happens to related records when a parent row is deleted. CASCADE deletes the children too, RESTRICT or the default NO ACTION blocks the delete, and SET NULL or SET DEFAULT keep the children but unlink them. Pick per relationship: cascade only for records that make no sense without the parent, restrict anything with business or financial history, and set null for optional links. Index the referencing columns too.

Why this decision matters

Foreign keys stop your data from pointing at things that don't exist, such as an order line for a deleted order. But when someone deletes a customer, a product or a location, the database needs instructions for the rows that refer to it. Get it wrong one way and a single admin click deletes years of invoices. Get it wrong the other way and staff can't remove a test product without a developer.

PostgreSQL's documentation on constraints lays out the options, and it's explicit that the right choice depends on what the related objects represent.

The options in plain English

Rule What happens when the parent is deleted Typical use
NO ACTION (default) Delete fails if children exist (checked at end of statement, or deferred) Safe default
RESTRICT Delete fails if children exist, checked immediately Records with history you must keep
CASCADE Children are deleted too Parts that can't exist alone
SET NULL Children stay; their reference becomes null Optional links
SET DEFAULT Children stay; reference becomes the column's default Reassign to a placeholder

The documentation notes that NO ACTION is the default and doesn't need to be written, and that the main difference from RESTRICT is that NO ACTION allows the check to be deferred until later in a transaction. In everyday use both prevent the delete.

When CASCADE is right

Cascade suits child records that are really part of the parent and have no meaning alone. PostgreSQL's own example uses order items referencing orders with ON DELETE CASCADE: if an order is removed, its line items go with it.

Other good fits:

  • A user's saved filters or dashboard settings.
  • Draft items attached to a draft document.
  • Temporary or session-level records.

Be careful with chains. If customers cascade to orders and orders cascade to order items, deleting one customer silently removes everything below it. Draw the chain before adding cascades.

When to restrict instead

Anything with financial, legal-record or audit value should block deletion. The same PostgreSQL example uses ON DELETE RESTRICT for products referenced by order items: you shouldn't be able to delete a product that's been sold, because past orders would lose their meaning.

Apply this to:

  • Customers with orders or invoices.
  • Products with sales history.
  • Staff referenced by bookings or approvals.
  • Locations referenced by stock movements.

For those, "delete" in your app usually shouldn't mean deleting the row at all. It means archiving: an archived_at column or a status that hides the record from everyday screens while keeping history intact.

When SET NULL fits

Use SET NULL when the link is optional information. The documentation's example is a product that references a manager: if the manager is deleted, the product should stay, just without a manager.

Similar cases:

  • A lead's "assigned to" salesperson.
  • A blog post's featured image.
  • A ticket's "referred by" field.

The column must allow nulls, and your app must handle the missing value gracefully, for example showing "Unassigned".

SET DEFAULT works the same way but uses the column's default, such as an "Unassigned" placeholder user. The documentation points out that the default must itself satisfy the foreign key, or the delete will fail.

Don't forget the index

A foreign key in PostgreSQL doesn't automatically create an index on the referencing columns; the documentation says so explicitly. Without one, every delete or key update on the parent has to scan the child table to check or apply the rule. On large tables that makes deletes slow and can lock things for longer than you'd expect.

Rule of thumb: index every foreign key column unless you're sure you never delete parents or look up children by it.

Decide in the app, too

Database rules are the safety net. Your app should still make the choice visible to people:

  • Show what will be affected before a delete: "This will remove 3 saved searches."
  • Offer archive instead of delete for records with history.
  • Give a clear message when a restricted delete fails, rather than a database error.

That way, the rule protects the data and the interface explains it.

How to review an existing schema

For an app that's already live:

  1. List every foreign key and its current ON DELETE rule.
  2. For each, ask: if the parent disappears, should the child go, stay unlinked or block the delete?
  3. Find cascade chains longer than one step and confirm they're intended.
  4. Check each referencing column has an index.
  5. Change rules one at a time in a migration, with a backup and a test on staging.

A worked example

Take a hypothetical Limerick catering-equipment supplier whose admin panel let staff delete customers. The customers table cascaded to orders, which cascaded to invoices. A staff member removed a duplicate customer record and took a year of invoices with it. After restoring from backup, the team changed customers-to-orders to RESTRICT, added an archive option to the admin panel, kept cascade only from orders to order lines, set the "account manager" link to SET NULL and indexed every foreign key.

FAQ

Is CASCADE ever dangerous on small apps?

Yes. The size of the app doesn't matter; one delete can remove more than intended through a chain.

Can I change an ON DELETE rule later?

Yes, by dropping and re-creating the constraint in a migration. Test it, as re-creating can take locks on large tables.

Should I use soft deletes everywhere instead?

Not everywhere. Archive records with history, and genuinely delete things that have no value once gone.

If you're not sure what a delete in your app really removes, ask us to review your foreign keys.

Sources

WhatsAppBook a call