Free tools Windows power users keep installed
One-click scans. No signup required.
To prevent an unintended hard delete from removing related rows, use a blocking foreign-key action such as RESTRICT or NO ACTION when those rows must be reviewed or retained. Reserve ON DELETE CASCADE for dependent records that should never outlive their parent. A soft delete—usually an update to a field such as deleted_at—does not itself trigger a foreign-key ON DELETE action.
What a foreign-key cascade actually does
A foreign key links a referencing row, such as an order item, to a referenced row, such as an order. Its ON DELETE action governs what the database does to referencing rows when the referenced row is physically deleted. PostgreSQL describes CASCADE as automatically deleting rows that reference the deleted row: PostgreSQL 18 constraints documentation.
This is a database-level hard-delete rule, not a general instruction to mark related records as deleted. If a parent row has several cascading foreign keys, one physical deletion can remove records along multiple relationship paths. Identify those paths before permitting the parent deletion.
Choose the action that matches the relationship
Use the most restrictive action that reflects the data relationship. A child that is merely related to a parent is not automatically disposable; cascade only when the child is a dependent component that should not exist on its own.
#1 Best Overall
| Action | Effect on referencing rows | When it may fit |
|---|---|---|
CASCADE |
Deletes the referencing rows when the referenced row is deleted. | Dependent component rows that have no meaningful independent lifecycle. |
RESTRICT |
Rejects deletion while referencing rows exist. | Relationships where existing references must block a parent deletion. |
NO ACTION |
Rejects deletion if references remain when the constraint is checked. | Use when the database’s checking timing matches the intended workflow; deferral differs by engine. |
SET NULL |
Preserves referencing rows but clears the foreign-key value. | Optional relationships whose foreign-key columns permit nulls and whose schema supports an unlinked row. |
For independent business entities, prefer a blocking action and require an explicit, reviewed process if deleting the parent is genuinely intended. PostgreSQL recommends considering RESTRICT or NO ACTION for relationships where cascading is not appropriate: PostgreSQL 18 constraints documentation.
Soft delete is separate from foreign-key delete behavior
A soft delete typically updates a row, for example by setting deleted_at. The row still exists, so that update is not the referenced-row deletion that invokes ON DELETE CASCADE. This follows from the database rule applying to deletion; it does not mean the database will automatically propagate a soft-delete marker.
Choose the soft-delete lifecycle deliberately. Decide whether related rows remain active, are also marked deleted by application logic, or are handled by a carefully designed trigger. Also define how queries hide marked rows and what restoring a parent means for children previously marked deleted. These are application and schema policy choices, not consequences of a foreign-key cascade.
PostgreSQL and MySQL behavior differs
PostgreSQL 18
NO ACTION is the default. If a constraint is configured as deferrable, its check may be deferred; RESTRICT prevents the operation immediately. PostgreSQL does not automatically create an index on the referencing columns, and recommends considering one for efficient lookups: constraints and foreign-key constraints.
Rank #3
MySQL 26.7
For InnoDB, NO ACTION is equivalent to RESTRICT. MySQL documents that foreign-key columns must be indexed and creates an index if needed. Confirm the storage engine and deployed MySQL version when choosing or changing an action: MySQL 26.7 foreign-key documentation.
Audit database constraints, ORM rules, and triggers
Inspect every foreign key on the delete path
For each table whose rows might be deleted, identify foreign keys that point to it and inspect the actual configured action. Do not assume the default is safe. Where related records must prevent deletion, use a blocking constraint; document any cascade as an intentional lifecycle rule so later schema changes do not silently widen the delete path.
Check ORM behavior separately
ORM relationship cascades and database foreign-key actions are different mechanisms. In SQLAlchemy 2.0, ORM delete cascade applies to unit-of-work deletion through Session.delete(); it does not apply to bulk delete statements. SQLAlchemy also documents how ORM relationship settings integrate with database-level ON DELETE behavior: SQLAlchemy 2.0 cascades. Audit both layers and verify the behavior of other ORM versions independently.
Review triggers and indexes
PostgreSQL warns that trigger code which modifies or blocks referential-action commands can interfere with referential integrity: PostgreSQL trigger behavior. Include relevant triggers in the review. Also account for indexing differences: PostgreSQL does not automatically index referencing columns, whereas MySQL documents automatic index creation when needed for foreign keys.
Recommended Free Tools
Validate the deployed behavior before changing deletion logic
- Inspect the deployed schema and list each foreign key reachable from the parent row, including its current
ON DELETEaction. - Review the application’s soft-delete and hard-delete paths, ORM relationship settings, bulk operations, and relevant triggers.
- Choose the database action from the relationship’s lifecycle: cascade only for dependent components, block deletion for retained references, or use
SET NULLonly for genuinely optional relationships. - Test the intended operation and failure cases against the same database engine and relevant storage configuration used in production, in a transaction or disposable environment. Include the actual ORM path as well as any direct or bulk database operation.
- Verify the resulting rows and constraint errors, then confirm that soft-delete, query filtering, and restoration behavior match the application’s stated policy.
Do not infer production behavior solely from an ORM declaration or from a different database engine. Constraint timing, supported actions, indexing, and ORM execution paths can change the result.
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.
