Course contentsShow
← Data Science
Lesson 1057 of 2,14527. Database Design and OptimizationFree lesson

ON DELETE and ON UPDATE Actions

Understanding CASCADE, SET NULL, SET DEFAULT, and RESTRICT actions when parent records are modified or deleted.

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.