Beginner LevelSQL & MySQL
How do ON DELETE CASCADE and ON DELETE SET NULL handle foreign key deletions?
❓ Interview Prompt: Explain Foreign Key deletion rules: `ON DELETE CASCADE`, `ON DELETE SET NULL`, `ON DELETE RESTRICT`, and `ON DELETE NO ACTION`.
Foreign key actions determine what happens to child records when a referenced parent record is deleted:
1
'ON DELETE CASCADE': Automatically deletes all child rows referencing the deleted parent row.
2
'ON DELETE SET NULL': Sets the foreign key column in child rows to 'NULL' when the parent row is deleted.
3
'ON DELETE RESTRICT' (Default): Prevents parent row deletion if any child rows reference it, throwing a foreign key violation error.
Code Implementation Example
sql
CREATE TABLE posts (
id BIGINT PRIMARY KEY,
user_id BIGINT,
title VARCHAR(255),
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE SET NULL -- Preserves posts when user is deleted!
);💡Setting foreign key delete action to ON DELETE SET NULL.
🎯 Interview Key Takeaway
Use ON DELETE CASCADE to remove dependent child data automatically, and ON DELETE SET NULL to preserve orphan child records.
#SQL#Foreign Key#CASCADE#SET NULL#Beginner
Back to all SQL & MySQL Questions→