How do foreign key constraints behave on DELETE and UPDATE operations (CASCADE, SET NULL, etc.)?

Question

Accepted Answer

Foreign key constraints define how MySQL should react when a referenced (parent table) row is deleted or updated. These actions ensure referential integrity is maintained. **CASCADE** → Automatically deletes or updates the child rows when the parent row changes. **SET NULL** → Sets the foreign key value in the child row to NULL. **RESTRICT** → Prevents the operation if child rows exist (default behavior in many cases). **NO ACTION** → Same as RESTRICT in MySQL; the delete/update is blocked. **SET DEFAULT** → Sets child rows to a default value (not supported by MySQL). CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
); **ON DELETE CASCADE** → Deleting a customer automatically deletes their orders. **ON UPDATE CASCADE** → Updating the customer's ID updates matching order.customer_id values. **ON DELETE SET NULL** → Deletes parent row but child.foreign_key becomes NULL. **RESTRICT / NO ACTION** → Operation is blocked if dependent rows exist. FOREIGN KEY (customer_id)
  REFERENCES customers(customer_id)
  ON DELETE SET NULL; These actions give fine-grained control over how relational data behaves when parent records change, ensuring data integrity according to the database design.

How do foreign key constraints behave on DELETE and UPDATE operations (CASCADE, SET NULL, etc.)?

Behavior of FOREIGN KEY Constraints on DELETE and UPDATE

Scenario Questions

Follow-up Questions