PostgreSQL: choosing ON DELETE RESTRICT, CASCADE or SET NULL without losing related records

Frame the delete policy as a data-retention decision

Every foreign key carries an implicit answer to a business question: when the referenced row disappears, what should happen to the rows that point at it? ON DELETE RESTRICT answers that the dependents must survive and the parent cannot be removed until those dependents are handled explicitly. ON DELETE CASCADE answers that the dependents are inseparable parts of the parent and should vanish together. ON DELETE SET NULL answers that the reference is optional and the dependent row can continue to exist without it. These are not interchangeable syntax options; they encode retention rules that affect what data remains queryable after a delete.

The PostgreSQL documentation states that the actions reflect intuitive choices: disallow deleting a referenced product, delete the orders as well, or something else. Picking the wrong one can silently remove rows that another relationship would need to preserve, or can block legitimate cleanup because dependents were never intended to survive.

This decision should follow the meaning of the relationship, not the convenience of the schema author. A product catalog entry referenced by historical orders is not the same as an order-item line that exists only because the order exists.

CREATE TABLE products (product_no integer PRIMARY KEY, name text, price numeric);
CREATE TABLE orders (order_id integer PRIMARY KEY, shipping_address text);
CREATE TABLE order_items (
  product_no integer REFERENCES products ON DELETE RESTRICT,
  order_id integer REFERENCES orders ON DELETE CASCADE,
  quantity integer,
  PRIMARY KEY (product_no, order_id)
);

Read the default NO ACTION behavior before changing it

If you write a foreign key without an ON DELETE clause, PostgreSQL applies ON DELETE NO ACTION. The documentation explains that this means the deletion in the referenced table is allowed to proceed, but the foreign-key constraint is still required to be satisfied, so the operation will usually result in an error. The key distinction from RESTRICT is timing and deferrability. NO ACTION checks the constraint at the end of the statement or at the end of the transaction if the constraint is deferrable, giving other commands a chance to fix the situation before the check fires. RESTRICT checks immediately and cannot be deferred.

Relying on the implicit default is fragile because a reader of the schema cannot tell whether the author intended a policy or simply forgot to specify one. Making the action explicit, even when it is NO ACTION, documents the decision and removes ambiguity during code review or migration.

-- These two are NOT equivalent:
-- product_no integer REFERENCES products  -- defaults to NO ACTION, deferrable
-- product_no integer REFERENCES products ON DELETE RESTRICT  -- immediate, non-deferrable

Choose RESTRICT when referenced rows must survive

Use ON DELETE RESTRICT when deleting a parent row while dependent rows exist should fail outright. This forces the application or operator to make an explicit decision about the dependents before the parent can be removed. The PostgreSQL documentation illustrates this with the product-and-order-items example: products and orders are different things, so making a deletion of a product automatically cause the deletion of some order items could be considered problematic.

RESTRICT is the right default for master data, reference tables, and any entity whose removal has downstream consequences that require human review. The error message from RESTRICT is deterministic and immediate, making it easy to catch in application code and present a meaningful message to the user.

-- Attempting this with RESTRICT on product_no will raise an error:
DELETE FROM products WHERE product_no = 7;
-- ERROR: update or delete on table "products" violates
--        foreign key constraint on table "order_items"

Choose CASCADE only for dependent lifecycle rows

ON DELETE CASCADE is appropriate when child rows exist only as part of the parent's lifecycle and have no independent meaning. The documentation notes that order items are part of an order, and it is convenient if they are deleted automatically if an order is deleted. The cascade travels from the referenced row to every referencing row in one operation.

The danger is that CASCADE can remove data that a different relationship would need to preserve. If an order_item row is also referenced by a shipping manifest or an audit log, cascading the delete from orders will remove the order_item and then violate or cascade further along those other foreign keys. Use CASCADE only when you have traced every downstream reference and confirmed that automatic removal is the intended behavior for all of them.

-- Deleting an order cascades to its line items:
DELETE FROM orders WHERE order_id = 42;
-- All rows in order_items with order_id = 42 are removed automatically.

Choose SET NULL only for optional references

ON DELETE SET NULL keeps the referencing row but sets the foreign-key column to NULL. This is appropriate when the relationship represents optional information. The documentation gives the example of a product manager reference: if the product manager entry gets deleted, setting the product's product manager to null might be useful.

SET NULL requires that the referencing columns accept NULL values. It cannot be used on a NOT NULL column or on a column that is part of a primary key without careful planning. For composite foreign keys, the column-list form lets you set only a subset of the referencing columns to NULL while leaving others intact, which is necessary when part of the composite key must remain populated.

-- Column-list form for composite foreign keys:
CREATE TABLE posts (
  tenant_id integer REFERENCES tenants ON DELETE CASCADE,
  post_id integer NOT NULL,
  author_id integer,
  PRIMARY KEY (tenant_id, post_id),
  FOREIGN KEY (tenant_id, author_id) REFERENCES users
    ON DELETE SET NULL (author_id)
);
-- Without the column list, tenant_id would also be set to NULL,
-- violating the primary key.

Compare the three actions on one small schema

Consider the products, orders, and order_items schema from the documentation. The order_items table has two foreign keys with different actions: product_no uses RESTRICT and order_id uses CASCADE. If you attempt DELETE FROM products WHERE product_no = 7 and that product appears in any order_items row, the statement fails immediately with a foreign-key violation error. No rows are removed from either table.

If you attempt DELETE FROM orders WHERE order_id = 42, the CASCADE action removes every order_items row referencing that order, and the delete succeeds. The same schema demonstrates both protective and automatic behavior depending on which parent you target.

SET NULL would apply if, for example, orders had an optional assigned_shipper column referencing a shipper table. Deleting a shipper would leave the order intact with assigned_shipper set to NULL, preserving the order history while removing the now-invalid reference.

-- Outcome table for the example schema:
-- DELETE FROM products WHERE product_no = 7
--   -> ERROR (RESTRICT blocks it, dependents survive)
-- DELETE FROM orders WHERE order_id = 42
--   -> SUCCESS (CASCADE removes matching order_items)
-- DELETE FROM shippers WHERE shipper_id = 3
--   -> SUCCESS (SET NULL clears orders.assigned_shipper)

Verify the policy before applying it to real data

Before altering a production constraint, inspect the current action with a query against pg_constraint or by reading the schema with \d in psql. Identify how many dependent rows exist for the parent rows you plan to delete. The documentation warns that writing DELETE FROM products without a WHERE clause removes all rows, so always scope your test deletes.

Run the intended DELETE inside a transaction that you roll back. This lets you observe whether RESTRICT raises an error, whether CASCADE removes the expected number of children, or whether SET NULL leaves rows with NULL references, all without committing changes. Only after confirming the behavior should you issue an ALTER TABLE to change the constraint action in production.

BEGIN;
DELETE FROM orders WHERE order_id = 42;
SELECT count(*) FROM order_items WHERE order_id = 42;
-- Expect 0 if CASCADE is correct.
ROLLBACK;
-- No data was permanently removed.

Know when the policy is insufficient

These actions govern what happens to referencing rows when a referenced row is deleted. They do not handle arbitrary cleanup, data retention schedules, or the removal of rows that are not connected by a foreign key. A CASCADE cannot compensate for an incorrect relationship model; if the child rows should not have been linked to the parent in the first place, no delete action will fix the underlying schema design.

The documentation also notes that ON UPDATE has related but not identical behavior, and that column lists cannot be specified for SET NULL and SET DEFAULT under ON UPDATE. A policy that works for deletes may not transfer directly to updates. Finally, an unbounded DELETE statement combined with CASCADE can remove large volumes of data in a single transaction, so application-level safeguards and explicit WHERE clauses remain necessary regardless of the constraint action.

-- This single statement could cascade through many tables:
DELETE FROM products;  -- no WHERE clause, caveat programmer
-- If CASCADE is set on multiple levels, this can be destructive.