Cascading Deletes and Updates
intermediateddl
2 min read
What are Cascading Deletes and Updates?
When a parent row is deleted or its primary key is updated, cascading actions automatically propagate the change to child rows that reference it via foreign keys.
Syntax
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
student_id INT REFERENCES students(id) ON DELETE CASCADE,
product_id INT REFERENCES products(id) ON DELETE SET NULL
);Actions Available
- CASCADE — Delete/update child rows automatically.
- SET NULL — Set the FK column to NULL.
- SET DEFAULT — Set the FK column to its default value.
- RESTRICT — Prevent the parent delete/update (immediate check).
- NO ACTION — Prevent the parent delete/update (deferred check, default).
When to Use
- CASCADE: When child rows have no meaning without the parent (enrollments when a student is deleted).
- SET NULL: When the child should remain but lose the reference.
- RESTRICT: When deletion of referenced parents should be blocked.
Key Points
- ON DELETE CASCADE — Deleting a student automatically deletes their enrollments.
- ON UPDATE CASCADE — Updating a PK propagates to FK columns (rare since PKs rarely change).
- Be Careful — CASCADE can delete many rows. Test before using in production.
- Mix Actions — Different FK columns can have different actions.
- RESTRICT vs NO ACTION — RESTRICT checks immediately; NO ACTION checks at end of statement (allows deferred constraints).
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Cascading Deletes and Updates Challenge
Write a query that solve this task: automatically remove enrollments when a student is deleted.
Expected result
Deleting a student or course automatically removes related enrollments.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are Cascading Deletes and Updates?
Initializing database...Each run starts from fresh sample data.
More Examples
SET NULL on delete
Keep orders but set product_id to NULL when a product is deleted.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Can CASCADE cause accidental mass deletion?
Yes. A single DELETE on a parent can cascade through multiple levels of child tables. Always verify cascade paths before implementing.
Can I add CASCADE to an existing foreign key?
You must drop and recreate the constraint: ALTER TABLE t DROP CONSTRAINT fk_name, ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent(id) ON DELETE CASCADE;