Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstalliTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Choose a PostgreSQL ON DELETE action by the relationship’s meaning: use CASCADE for dependent components, SET NULL for an optional reference, and RESTRICT when deletion must stop until references are handled explicitly. PostgreSQL’s default, NO ACTION, also rejects a delete that leaves references when the constraint is checked, but a deferrable NO ACTION check can wait until later in the transaction.
How the four actions differ
These actions define what happens to rows in the referencing table when a row in the referenced table is deleted. The choice affects whether the referencing rows survive, whether their foreign-key values survive, and whether PostgreSQL blocks the delete.
| Action | What happens to referencing rows | What happens to the foreign-key value | Can the check be deferred? |
|---|---|---|---|
CASCADE |
Deleted automatically | Not applicable; the referencing row is deleted | Not applicable to the delete action itself |
SET NULL |
Remain in the table | The selected foreign-key column or columns are set to NULL |
Not applicable to the delete action itself |
RESTRICT |
Remain; the referenced-row delete is rejected if references exist | Unchanged | No. PostgreSQL does not defer the RESTRICT check |
NO ACTION (default) |
Remain; the operation fails if references still exist when checked | Unchanged | Yes, if the foreign-key constraint is deferrable |
The distinction between RESTRICT and NO ACTION matters in transactions: a deferred NO ACTION constraint allows other commands in the transaction to repair or remove references before the check occurs. RESTRICT does not permit that delay.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
When to use ON DELETE CASCADE
Use CASCADE when referencing rows are dependent components that have no useful independent lifecycle. For example, order items may exist only as parts of an order, so deleting the order can reasonably delete its items as well. PostgreSQL’s guidance is to base the action on what kinds of objects the related tables represent; see the PostgreSQL 18 constraints documentation.
#1 Best Overall
Avoid cascading between independent entities if deleting one should not silently erase the other entity’s records. Consider the full chain of foreign keys too: a single delete can affect multiple dependent rows through cascades.
When to use ON DELETE SET NULL
Use SET NULL when the referencing row remains meaningful after the referenced object is gone, but the association was optional. For example, a record may remain after the user who created it is deleted, with its creator reference cleared.
Rank #2
- The affected foreign-key column must allow
NULL. - Other constraints on the row must allow the resulting state.
- For a composite foreign key, PostgreSQL can set only specified columns to null. This is useful when another column in the key also serves a separate role, such as participating in a primary key.
When to use ON DELETE RESTRICT
Use RESTRICT when a referenced row must not be deleted while any referencing rows exist. The delete is rejected, leaving the application to decide what to do first—for example, reassign those rows or delete them through an explicit operation. The check cannot be deferred.
When to use the default, ON DELETE NO ACTION
NO ACTION is PostgreSQL’s default when no delete action is specified. If references still exist when the foreign-key constraint is checked, PostgreSQL rejects the operation. With a deferrable constraint, the check can be postponed until later in the transaction, so commands can first repair or remove the references. Use this distinction only when the transaction workflow benefits from that flexibility; otherwise, RESTRICT and NO ACTION both prevent a delete that would leave invalid references.
A practical decision process
- Ask whether the referencing row can stand alone. If it is a dependent component, choose
CASCADE. If it remains useful without the relationship, continue. - Ask whether the relationship is optional. If it is, and the row can validly have a null foreign key, choose
SET NULL. - Decide who should resolve remaining references. Choose
RESTRICTwhen deletion must fail immediately so the application can handle them first. ChooseNO ACTIONwhen the default behavior is suitable, or when a deferrable check is useful within a transaction. - Check the resulting row against all constraints. Confirm nullability and any other relevant constraints, particularly when using
SET NULL.
Remember the referencing-column index
PostgreSQL requires the referenced key to be backed by a primary key, unique constraint, or qualifying unique index. It does not automatically index the referencing columns. An index on those columns can make it more efficient to find referencing rows during deletes of referenced rows or updates of referenced keys; whether to add one depends on the workload and the rest of the indexing strategy.
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.
