keepcalculator

06 Numbers and Powers

PostgreSQL ON DELETE: SET NULL vs CASCADE vs RESTRICT

Card No. 06·80748 · Updated · Standard formula, shown below

Use SET NULL when a deleted parent row should leave the child row intact but with a missing reference; use CASCADE when deleting a parent should automatically delete its children; use RESTRICT (or NO ACTION) when you want to prevent deletion of a parent that still has referencing rows. The right choice depends on your data model and business rules, and this article explains each option in depth so you can decide confidently.

Background: Foreign Keys and Referential Actions

In relational databases, a foreign key constraint links a column (or columns) in one table to the primary key of another. This ensures that every value in the foreign key column exists in the referenced table, preserving referential integrity. When you delete a row from the parent (referenced) table, you must decide what happens to child (referencing) rows. PostgreSQL offers several referential actions to handle this: ON DELETE CASCADE, ON DELETE SET NULL, ON DELETE RESTRICT, ON DELETE NO ACTION, and ON DELETE SET DEFAULT. Each action defines a different behavior for the child rows when the parent is deleted.

How Each Action Works

Let’s examine each option with a concrete example. Suppose you have two tables: authors and books. The books table has a foreign key author_id referencing authors(id).

  • CASCADE: When you delete an author, PostgreSQL automatically deletes all books written by that author. This is useful when child rows have no meaning without their parent (e.g., order items without an order).
  • SET NULL: When you delete an author, the author_id column in all their books is set to NULL. The books remain, but they become “orphaned” (they no longer point to an author). This requires the foreign key column to be nullable.
  • RESTRICT: If any books reference an author, the delete operation is aborted with an error. You must first delete or update the books manually. RESTRICT is checked immediately, so you cannot defer the check to the end of the transaction.
  • NO ACTION: This is the default if you omit an action. It behaves like RESTRICT in most cases, but the check can be deferred to the end of the transaction if you declare the constraint as DEFERRABLE. In practice, for immediate checks, NO ACTION and RESTRICT are identical.
  • SET DEFAULT: When the parent is deleted, the foreign key column is set to its default value. This is rarely used because the default value must exist in the parent table, and it can lead to surprising behavior.

How to Choose: Matching Actions to Business Rules

The decision hinges on the relationship’s semantics. Ask yourself: “If the parent disappears, should the child still exist?”

  • Use CASCADE when the child is a direct part of the parent and cannot exist independently. Example: orders and order_items — deleting an order should delete its items.
  • Use SET NULL when the child can exist without the parent, but you want to preserve the child’s data. Example: employees and projects — if a project manager leaves, the project remains but the manager reference becomes NULL.
  • Use RESTRICT or NO ACTION when you want to prevent deletion of a parent that still has children, forcing the application to handle the children first. Example: countries and cities — you don’t want to accidentally delete a country while it still has cities; you’d rather get an error and decide manually.

Common Mistakes and Misconceptions

One frequent mistake is assuming NO ACTION and RESTRICT are completely different. They are not — the only difference is deferrability. Another misconception is that SET NULL requires the column to be nullable; if the column has a NOT NULL constraint, the delete will fail with an error. Also, developers sometimes forget that CASCADE can chain: if table A references table B with CASCADE, and table B references table C with CASCADE, deleting from C will cascade through B to A. This can be powerful but also dangerous if you don’t fully map the dependency graph.

Performance and Locking Considerations

From a performance standpoint, CASCADE can be efficient because PostgreSQL performs the child deletions in a single statement, but it acquires locks on all affected rows. SET NULL requires updating child rows, which can be heavy if there are many children. RESTRICT and NO ACTION are fast when no children exist, but they force you to do extra work when children exist. In high-concurrency environments, deferring constraints with NO ACTION DEFERRABLE can reduce lock contention because the check happens at commit time, but it can also lead to unexpected failures at commit.

Alternatives and Advanced Patterns

Sometimes you don’t want any automatic action. Instead, you can use a trigger to implement custom logic, such as archiving child rows before deletion or logging the action. Another alternative is to use a soft delete pattern: add a deleted_at timestamp to the parent table and filter it out in queries, leaving the foreign key intact. This avoids the need for ON DELETE actions altogether. For complex schemas, consider using ON DELETE SET NULL with a nullable foreign key and then a partial unique index to ensure only one “orphaned” row per parent, if that fits your logic.

Safety and Data Integrity

Before choosing an action, consider the risk of accidental data loss. CASCADE is the most dangerous because a single delete can wipe out large portions of related data. Always test with a transaction (BEGIN; ... ROLLBACK;) to see the effect. Also, be aware that SET NULL can break NOT NULL constraints and may require you to handle nulls in your application logic. For critical systems, prefer RESTRICT or NO ACTION to force explicit handling, and use CASCADE only when the relationship is truly compositional.

Frequently Asked Questions

What is the difference between RESTRICT and NO ACTION?

In PostgreSQL, the only difference is that NO ACTION can be deferred if the constraint is declared DEFERRABLE. With immediate checking, they behave identically: the delete is aborted if referencing rows exist. RESTRICT cannot be deferred.

Can I use SET NULL if the foreign key column is NOT NULL?

No. If you try to delete a parent row with children, PostgreSQL will raise an error because it cannot set the column to NULL while the NOT NULL constraint is in place. You must either make the column nullable or use a different action.

Does CASCADE delete children in a specific order?

PostgreSQL handles the order internally, but you should not rely on it. If you have multiple cascading paths, the system will follow the dependency graph. In practice, you should design your schema to avoid ambiguous cascades.

What happens if I delete a parent with SET NULL and the child has other constraints?

Setting the foreign key to NULL only affects that column. Other constraints on the child row remain valid. However, if the child has a unique constraint that includes the foreign key, multiple NULL values are allowed (in standard SQL, NULLs are distinct), so that’s usually fine.

Can I change the ON DELETE action after creating the table?

Yes, you can alter the foreign key constraint using ALTER TABLE ... DROP CONSTRAINT ... ADD CONSTRAINT ... with the new action. This is a DDL operation that may lock the table, so plan it during a maintenance window.

Is CASCADE always the best for performance?

Not necessarily. While CASCADE can be efficient for small child sets, it can cause long-running transactions and heavy locking when there are many children. SET NULL requires updates to child rows, which can also be costly. Measure with your actual data.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →