Cascading Actions: DELETE and UPDATE
What you'll learn: How to automatically maintain referential integrity when parent records are modified or deleted using CASCADE, SET NULL, and RESTRICT options.
When you delete or update a row in a parent table that has related children via foreign keys, SQL needs to know what to do with those orphaned child records. Should they be deleted too? Should their references be cleared? Or should the operation be blocked entirely?
Understanding Your Options
Think of a company hierarchy: if a department is deleted, what happens to the employees in that department?
CASCADE automatically propagates the change to child records:
ON DELETE CASCADE: Deleting a parent deletes all childrenON UPDATE CASCADE: Updating a parent's primary key updates all matching foreign keys
SET NULL clears the foreign key in child records:
ON DELETE SET NULL: Deleting a parent sets children's foreign keys to NULLON UPDATE SET NULL: Updating a parent's key sets children's foreign keys to NULL
RESTRICT (or NO ACTION) prevents the parent operation if children exist:
- Blocks the DELETE or UPDATE entirely, preserving data integrity by forcing you to handle children first
Example Declaration
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE
ON UPDATE RESTRICT
);
This means: if a customer is deleted, all their orders vanish too. But you cannot change a customer's ID if they have orders.
Key Takeaway: Cascading actions automate referential integrity maintenance—CASCADE propagates changes, SET NULL clears references, and RESTRICT blocks operations to prevent data inconsistency. Choose based on your business logic and data preservation needs.