Cascading Actions

Definition

Cascading actions tell the database what to do to child rows automatically when a referenced parent row is updated or deleted, instead of simply rejecting the operation. They're specified as part of a FOREIGN KEY declaration using ON DELETE / ON UPDATE clauses.

How It Works

sql
CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(100));

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(id)
    ON DELETE CASCADE       -- deleting a department deletes its employees too
    ON UPDATE CASCADE       -- if a department's id ever changes, employees follow automatically
);

The main options, and what each means for employees when the referenced departments row is deleted:

• `RESTRICT` (often the default) — refuse the delete outright if any employees still reference this department.
• `CASCADE` — delete the referencing employees too, automatically.
• `SET NULL` — set employees.department_id to NULL for those rows (the column must allow NULL).
• `NO ACTION` — similar to RESTRICT in most engines (the exact timing difference is a subtle, engine-specific detail).

Edge Cases and Pitfalls

• ON DELETE CASCADE is powerful and genuinely convenient, but also genuinely dangerous: deleting one department row can silently delete an entire tree of dependent data (employees, and anything THOSE rows cascade to in turn) — always confirm the intended blast radius before relying on it, especially in a chain of several cascading tables.
• SET NULL requires the foreign key column to actually allow NULL — declaring ON DELETE SET NULL on a NOT NULL foreign key column is a contradiction that will fail as soon as the cascading action is actually triggered.
• Choosing RESTRICT (the safe default in many systems) means the application must handle the failure explicitly (e.g. "you must reassign or remove these employees before deleting this department") — it's more manual work, but far less likely to destroy data by accident.

Key Takeaways

• Cascading actions (RESTRICT/CASCADE/SET NULL/NO ACTION) decide what happens to child rows when a parent row is deleted or updated.
• CASCADE is convenient but can propagate deletes far further than expected — verify the full dependency chain.
• SET NULL only works if the foreign key column permits NULL values.