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

Cascading Actions: DELETE and UPDATE

Understand CASCADE, SET NULL, and RESTRICT options for maintaining consistency when parent records change.

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 children
  • ON 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 NULL
  • ON 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.