ON DELETE and ON UPDATE Actions
You'll learn: How to control what happens to related records when a parent row is modified or deleted using CASCADE, SET NULL, SET DEFAULT, and RESTRICT actions.
When you define a foreign key relationship, you're linking a child table to a parent table. But what should happen when someone deletes or updates a row in the parent table? SQL gives you four actions to handle this scenario automatically.
The Four Actions
CASCADE — Think of it like falling dominoes. When the parent row is deleted or updated, the change automatically cascades to all child rows. Delete a customer? All their orders are deleted too. Update a product ID? All order items using that ID get updated automatically.
SET NULL — When the parent row is deleted or updated, the foreign key columns in child rows are set to NULL. Like removing a manager from the org chart but keeping the employee records (with manager_id set to NULL).
SET DEFAULT — Similar to SET NULL, but sets the foreign key to a predefined default value instead. Useful when you have a "fallback" option, like reassigning orphaned records to a generic category.
RESTRICT — The strict parent. It prevents any deletion or update of the parent row if child rows still reference it. You must handle the children first. This is often the safest default to prevent accidental data loss.
Choosing Wisely
Your choice depends on your business logic. CASCADE is convenient but dangerous—one delete can wipe out thousands of related records. RESTRICT is conservative and forces explicit handling. SET NULL works when relationships are optional.
Key Takeaway: ON DELETE and ON UPDATE actions (CASCADE, SET NULL, SET DEFAULT, RESTRICT) give you fine-grained control over referential integrity, automating how child records respond when parent records change—choose based on your data protection and business logic needs.