What Do ON DELETE CASCADE, SET NULL, and RESTRICT Mean for Foreign Keys?
Understand ON DELETE CASCADE, SET NULL, and RESTRICT for SQL foreign keys, with examples showing when to use each.
Expected Interview Answer
These are referential actions attached to a foreign key that tell the database what to do to child rows when the referenced parent row is deleted or updated: CASCADE propagates the change to child rows, SET NULL clears the foreign key column in child rows, and RESTRICT (or the default NO ACTION) blocks the operation entirely if dependent child rows still exist.
Without a referential action, deleting a parent row that still has children referencing it would either fail or leave orphaned foreign keys pointing at nothing, breaking referential integrity. ON DELETE CASCADE automatically deletes every child row referencing the deleted parent, which is appropriate for true ownership relationships like an order and its order lines, but dangerous for loosely related data since it can silently wipe out large amounts of related data. ON DELETE SET NULL instead clears the foreign key column on child rows, appropriate when the relationship is optional and the child can meaningfully exist without a parent, such as an employee whose manager_id becomes NULL when that manager is deleted. RESTRICT or NO ACTION refuses the delete outright while dependent rows exist, forcing an explicit decision, which is the safest default when accidental data loss is the primary concern.
- CASCADE keeps tightly owned child data automatically consistent
- SET NULL preserves child rows while safely detaching an optional relationship
- RESTRICT prevents accidental, silent loss of related data
- Choosing the right action encodes the true relationship semantics in the schema
AI Mentor Explanation
When a franchise is dissolved from a league, CASCADE is like automatically releasing every player under contract to that franchise, since a player contract cannot exist without the franchise it belongs to. SET NULL is like clearing a player's franchise field back to free-agent status instead of releasing them from the league. RESTRICT is like the league office refusing to dissolve a franchise at all while it still has contracted players, forcing administrators to reassign the roster first.
Foreign Key Referential Actions on Parent Row Deletion
CASCADE
- Deletes all matching child rows automatically
SET NULL
- Clears the foreign key column on matching child rows
RESTRICT / NO ACTION
- Blocks the delete while matching child rows exist
Step-by-Step Explanation
Step 1
Identify the relationship type
Decide whether the child truly cannot exist without the parent (ownership) or can exist independently (loose association).
Step 2
Choose CASCADE for ownership
Use ON DELETE CASCADE when child rows have no meaning once the parent is gone, like order lines under an order.
Step 3
Choose SET NULL for optional links
Use ON DELETE SET NULL when the child should survive with the relationship simply cleared, like an employee losing a deleted manager.
Step 4
Choose RESTRICT for safety
Use RESTRICT or NO ACTION as the default when accidental cascading deletes would be more dangerous than a blocked operation.
What Interviewer Expects
- Correct explanation of all three referential actions and when each fires
- A concrete example distinguishing ownership from optional association
- Awareness that RESTRICT/NO ACTION is often the safer default in production schemas
- Understanding that these actions apply to ON UPDATE as well as ON DELETE
Common Mistakes
- Using CASCADE everywhere without considering accidental mass deletion
- Confusing SET NULL with CASCADE and expecting rows to be deleted
- Forgetting that SET NULL requires the foreign key column to be nullable
- Not distinguishing ON DELETE actions from ON UPDATE actions on the same foreign key
Best Answer (HR Friendly)
“These are rules I put on a foreign key to control what happens to related rows when a parent row is deleted. CASCADE deletes the related rows too, which I use for true ownership like order items under an order. SET NULL just clears the link and keeps the related row, which I use for optional relationships like an employee's manager. RESTRICT blocks the delete entirely if related rows still exist, which I lean on by default to avoid accidental data loss.”
Code Example
-- CASCADE: deleting an order removes its order lines automatically
CREATE TABLE OrderLines (
order_line_id INT PRIMARY KEY,
order_id INT NOT NULL,
FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE
);
-- SET NULL: deleting a manager just clears manager_id, employee stays
CREATE TABLE Employees (
employee_id INT PRIMARY KEY,
manager_id INT NULL,
FOREIGN KEY (manager_id) REFERENCES Employees(employee_id) ON DELETE SET NULL
);
-- RESTRICT (default in many engines): blocks deleting a customer with orders
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE RESTRICT
);Follow-up Questions
- What is the difference between RESTRICT and NO ACTION in most engines?
- Can ON UPDATE CASCADE and ON DELETE CASCADE be configured independently on the same foreign key?
- What must be true about a column for ON DELETE SET NULL to work?
- What risk does ON DELETE CASCADE introduce in a deeply nested table hierarchy?
MCQ Practice
1. What does ON DELETE CASCADE do when a referenced parent row is deleted?
CASCADE propagates the delete to every matching child row automatically, appropriate for true ownership relationships.
2. For ON DELETE SET NULL to work correctly, what must be true of the foreign key column?
SET NULL requires the foreign key column to allow NULL values, since that is the value it will be set to.
3. What happens under ON DELETE RESTRICT if child rows still reference the parent being deleted?
RESTRICT (and typically NO ACTION) prevents the delete from completing while dependent child rows still exist.
Flash Cards
What does ON DELETE CASCADE do? — Automatically deletes child rows that reference the deleted parent row.
What does ON DELETE SET NULL do? — Clears the foreign key column on child rows, keeping the child rows themselves.
What does ON DELETE RESTRICT do? — Blocks the delete entirely while dependent child rows still exist.
When is CASCADE risky? — When applied to loosely related data, since it can silently delete large amounts of unrelated-feeling data.