Choose a PostgreSQL ON DELETE action according to what the related rows mean: use CASCADE for dependent components, SET NULL for optional references, and RESTRICT when a delete must stop until the application handles existing references. PostgreSQL’s default, NO ACTION, also rejects a delete that leaves references, but a deferrable constraint can postpone that check until later in the transaction.
How the four actions differ
A foreign key links referencing rows to a referenced row. When someone tries to delete the referenced row, its ON DELETE action determines what PostgreSQL does with those references.
As an Amazon Associate I earn from qualifying purchases.
| Action | Referencing rows | Foreign-key value | Effect on delete | Can the check be deferred? |
|---|---|---|---|---|
CASCADE |
Deleted | Row is removed | Deletion propagates to referencing rows | Not applicable as a rejection check |
SET NULL |
Kept | Set to NULL in the affected column or columns |
Referenced row can be deleted if the resulting row satisfies constraints | Not applicable as a rejection check |
RESTRICT |
Kept | Unchanged | Blocks deletion when references exist | No |
NO ACTION (default) |
Kept | Unchanged | Raises an error if references remain when the constraint is checked | Yes, if the constraint is deferrable |
PostgreSQL’s guidance is that the right action depends on “what kinds of objects the related tables represent.” PostgreSQL 18: Constraints.
Recommended Free Tools
When to use each action
Use CASCADE for dependent components
Choose ON DELETE CASCADE when the referencing row is a component that has no useful independent existence. PostgreSQL’s example is order items belonging to an order: deleting the order can delete its items automatically.
#1 Best Overall
Avoid cascading between independent entities if deleting one should not silently erase records belonging to the other. The convenience of automatic cleanup is also a responsibility: the delete can affect multiple referencing rows.
Use SET NULL for an optional relationship
Choose ON DELETE SET NULL when the referencing row remains meaningful after the referenced object is gone, but its association should be cleared. For example, an optional attribution can be removed while retaining the associated record.
Rank #2
- The affected foreign-key column must permit
NULL. - Other constraints must allow the resulting row state.
- For a composite foreign key, PostgreSQL allows specifying which columns are set to null. This is useful when another foreign-key column also participates in a primary key.
Use RESTRICT when a delete must stop immediately
Choose ON DELETE RESTRICT when the referenced row must not be deleted while references exist. The application must first make an explicit decision—for example, reassign or delete the referencing rows. PostgreSQL does not allow the RESTRICT check to be deferred.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsKeep NO ACTION for the default, or defer its check when needed
NO ACTION is PostgreSQL’s default. If references still exist when the constraint is checked, the delete fails. Unlike RESTRICT, a deferrable NO ACTION constraint can have its check postponed until later in the transaction. That can allow other commands in the same transaction to remove or repair the references before the check occurs.
A practical decision path
- Ask whether the referencing row has its own lifecycle. If it is only a component of the referenced row, choose
CASCADE. - If it survives independently, ask whether the reference is optional. If so, choose
SET NULLonly after confirming nullability and other constraints. - If the reference must be handled explicitly, decide when the check should happen. Choose
RESTRICTto block the delete immediately; choose deferrableNO ACTIONif the transaction needs to repair or remove references before the check.
Check the foreign-key index separately
PostgreSQL requires the referenced key to be backed by a primary key, unique constraint, or qualifying unique index. It does not automatically create an index on the referencing columns. An index there can make it more efficient to find referencing rows when deleting referenced rows or updating referenced keys; whether it is worthwhile depends on the workload and indexing choices. PostgreSQL 18 documentation on foreign-key constraints.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
