Databaseintermediate

Cascading Deletes

Automatically deleting child rows when a parent row is deleted, enforced by foreign key constraints with ON DELETE CASCADE.

Also known as: ON DELETE CASCADE, cascade operations, referential actions

Description

Cascading deletes propagate a DELETE operation from a parent table to all child tables linked by foreign keys with ON DELETE CASCADE. When you delete a customer, all their orders, order items, and payment records are automatically deleted in a single atomic operation. This keeps the database consistent without requiring the application to manually delete related records in the correct order.

Cascading deletes are convenient but dangerous at scale. Deleting a single parent row can trigger the deletion of thousands of child rows across multiple tables, holding locks for extended periods and potentially causing timeouts or replication lag. In deep hierarchies (tenant -> organizations -> teams -> users -> user_settings), a cascade can fan out exponentially. Always map out the full cascade graph before adding ON DELETE CASCADE and estimate the maximum number of rows that could be affected.

Alternatives to ON DELETE CASCADE include: ON DELETE RESTRICT (block the delete if children exist, forcing the application to handle cleanup explicitly), ON DELETE SET NULL (set the FK to NULL, orphaning the child but preserving it), and application-level cascading with batched deletes (delete children in chunks of 1000 within separate transactions to avoid long-running locks). For soft-delete systems, cascading deletes are typically handled in application code by propagating the deleted_at timestamp to related records.

Prompt Snippet

Use ON DELETE CASCADE only for tightly coupled parent-child relationships where children have no meaning without the parent (e.g., order -> order_items). For loosely coupled relationships, use ON DELETE RESTRICT and implement explicit application-level deletion with batched DELETEs (1000 rows per transaction) to control lock duration. Before adding CASCADE to any FK, query the child table to determine the maximum cascade fan-out: SELECT parent_id, COUNT(*) FROM children GROUP BY parent_id ORDER BY count DESC LIMIT 1. If max fan-out exceeds 10K rows, use application-level batched deletion instead.