CASCADE Referential Action

A CASCADE referential action makes qualifying changes to a referenced parent row automatically propagate to matching rows in a child table. It is commonly configured with foreign keys using `ON DELETE CASCADE` or `ON UPDATE CASCADE`.

A foreign key establishes a relationship in which values in a child table reference candidate key values in a parent table. A referential action defines what the database should do when the referenced key is deleted or updated.

With `ON DELETE CASCADE`, deleting a parent row causes matching child rows to be deleted automatically. This is useful when child records have no meaningful existence without their parent, such as order items belonging to an order.

With `ON UPDATE CASCADE`, changing a referenced key value causes the corresponding foreign-key values in child rows to change automatically. In practice, primary keys are usually stable, so `ON UPDATE CASCADE` is encountered less often than delete cascading.

Cascade behavior is enforced by the database's foreign-key constraint, rather than by application code issuing separate delete or update statements. Cascading can traverse multiple related tables, so a change can have effects beyond the directly referenced child table.

Key points

• `ON DELETE CASCADE` removes dependent child rows when the referenced parent row is deleted.
• `ON UPDATE CASCADE` propagates changes to referenced key values into foreign-key columns.
• Cascade is a foreign-key referential action enforced by the database.
• Use cascading when dependent rows should not survive independently of their parent.
• Cascading operations can propagate through multiple foreign-key relationships.

Interview questions

What does `ON DELETE CASCADE` do?

`ON DELETE CASCADE` is a foreign-key referential action. When a referenced parent row is deleted, matching child rows are automatically deleted by the database. It is appropriate when those child rows are dependent data with no independent meaning. The database performs the propagation, so application code does not need separate child-delete statements.

What is the difference between `ON DELETE CASCADE` and `ON UPDATE CASCADE`?

`ON DELETE CASCADE` responds when a referenced parent row is deleted and removes corresponding child rows. `ON UPDATE CASCADE` responds when the referenced key value changes and updates matching foreign-key values in child rows. Delete cascading is more common because primary keys are generally intended to remain stable. Both are configured as foreign-key referential actions.

When is `ON DELETE CASCADE` a good design choice?

It fits relationships where the child has no useful independent lifetime after its parent disappears. A typical example is order items belonging to an order, assuming the application's data-retention requirements permit their deletion. It can simplify application logic and keep dependent data synchronized with the parent. However, it should not be used merely for convenience when child records must be retained for audit, compliance, or historical purposes.

What happens when cascading foreign keys form multiple levels of dependencies?

A parent operation can trigger referential actions on directly related rows, which can in turn affect rows in other related tables. The resulting operation can therefore propagate through a dependency graph rather than stopping at one child table. Database-specific rules govern whether a particular foreign-key configuration is permitted, especially for cycles or multiple cascade paths. Designers should understand the complete dependency structure before introducing broad cascading behavior.