Choose a PostgreSQL foreign-key deletion policy without losing the wrong rows

Decide whether the child can exist on its own

A detail row that has no meaning without its parent may suit cascading deletion. A historical record that must remain may require restricting deletion or retaining the row with an optional relationship. Start with that business decision instead of picking the action that makes a failing DELETE succeed.

A foreign key protects the relationship declared in its constraint. It does not by itself enforce a retention policy, obtain permission to remove personal data, or preserve external files associated with the record.

Inspect the constraint that actually applies

Find the referenced table, referencing columns and configured ON DELETE action in the database schema or administrative tool. Do not infer the action from the name of a column or from an ORM model that may not match the deployed database.

CASCADE and SET NULL belong after the REFERENCES clause in the foreign-key definition. A command such as DELETE FROM parent WHERE id = 1 CASCADE is not PostgreSQL DELETE syntax for choosing a foreign-key action.

Compare the three actions

With RESTRICT, an attempt to remove a parent still referenced by a child is rejected. With CASCADE, deletion of the parent removes referencing children. With SET NULL, the children remain but their referencing columns become null. Other constraints still apply and can prevent the operation.

SET NULL is suitable only when the affected columns and application logic accept null. A NOT NULL constraint can make the deletion fail. NO ACTION is another policy, but it should not be described as identical to RESTRICT in every situation: deferred constraint checking can make the distinction important.

Trace an isolated example

This illustrative script uses three different child tables and three different parent rows to keep the policies independent. It does not attach three conflicting policies to one relationship. Run such demonstrations only in a disposable session; the transaction ends with ROLLBACK and does not claim to test your production schema.

BEGIN;
CREATE TEMP TABLE demo_parent (id integer PRIMARY KEY);
CREATE TEMP TABLE demo_restrict (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES demo_parent(id) ON DELETE RESTRICT
);
CREATE TEMP TABLE demo_cascade (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES demo_parent(id) ON DELETE CASCADE
);
CREATE TEMP TABLE demo_null (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES demo_parent(id) ON DELETE SET NULL
);
INSERT INTO demo_parent VALUES (1), (2), (3);
INSERT INTO demo_restrict VALUES (10, 1);
INSERT INTO demo_cascade VALUES (20, 2);
INSERT INTO demo_null VALUES (30, 3);
DELETE FROM demo_parent WHERE id IN (2, 3);
SELECT count(*) AS remaining_cascade_rows FROM demo_cascade;
SELECT id, parent_id FROM demo_null ORDER BY id;
ROLLBACK;

Understand the expected result and the rejected case

The expected cascade count is zero because the child linked to parent 2 is removed. The row with id 30 remains in demo_null with parent_id equal to null. The script does not delete parent 1, so its restrict child remains. These results follow from the stated constraints and sample data; they are illustrative, not measurements.

Trying to delete parent 1 before rollback would be rejected because demo_restrict still references it. That deliberately failing statement is omitted from the script: an error can abort the transaction, and subsequent SQL needs appropriate rollback handling.

Check the scope before a real deletion

Count the rows matching the proposed parent predicate and inspect dependent tables. Cascades can continue through further relationships and remove more rows than a single child-table count suggests. Review the complete dependency chain and the application consequences.

A preview query and a later DELETE are not automatically a protected atomic decision under concurrent changes. Plan the required transaction and locking behavior for the real operation rather than assuming that an earlier count freezes the data.

Check performance without claiming a guaranteed speed

Deleting a referenced row can require finding rows on the referencing side. PostgreSQL does not automatically create an index on the referencing foreign-key columns merely because you declare the foreign key. Check existing indexes and the workload before deciding whether another index is appropriate.

Large cascades can hold locks, produce substantial database work and affect concurrent users. Use an appropriate maintenance procedure and a recovery plan. The small temporary-table example establishes behavior, not the cost of a production deletion.

Keep relational actions separate from application cleanup

Database constraints act on the declared database relationships. They do not guarantee cleanup of files, remote services or events maintained outside the transaction. Account for those side effects explicitly.

Rollback protects transactional database changes in the example, not arbitrary actions performed by external systems. If a deployed policy is wrong, treat changing it as a reviewed schema change rather than a quick workaround for one failing request.