ON DELETE CASCADE / ON UPDATE CASCADE
CASCADE rules automate how child table rows respond when a parent row is deleted or updated. ON DELETE CASCADE removes child rows. ON UPDATE CASCADE updates child foreign keys when the parent key changes. These are powerful tools that require careful planning.
CASCADE Options Compared
- ON DELETE CASCADE - delete children when parent deleted
- ON DELETE SET NULL - set FK to NULL (FK column must allow NULL)
- ON DELETE SET DEFAULT - set FK to its DEFAULT value
- ON DELETE RESTRICT - block deletion if children exist (safe default)
- ON DELETE NO ACTION - same as RESTRICT, checked at end of transaction
- ON UPDATE CASCADE - update child FK when parent PK changes (rare)
- ON UPDATE SET NULL - set child FK to NULL on parent update
CASCADE Examples
-- CASCADE setup in ecommerce_db
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id)
ON DELETE CASCADE -- delete orders when user deleted
ON UPDATE CASCADE, -- update user_id if users.id changes
total_amount DECIMAL(10,2),
status VARCHAR(50)
);
CREATE TABLE payments (
id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(id)
ON DELETE CASCADE, -- delete payment when order deleted
amount DECIMAL(10,2),
payment_method VARCHAR(50)
);
-- Demonstration: delete user 1 → cascades
DELETE FROM users WHERE id = 1;
-- Automatically deletes: orders where user_id=1
-- Then automatically deletes: payments where order_id in deleted orders
-- SET NULL: keep orders but remove user link
CREATE TABLE orders (
user_id INT REFERENCES users(id) ON DELETE SET NULL
);
-- When user deleted: orders remain with user_id = NULL
-- Modify existing FK to add CASCADE (PostgreSQL)
ALTER TABLE orders DROP CONSTRAINT orders_user_id_fkey;
ALTER TABLE orders ADD CONSTRAINT orders_user_id_fkey
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;Quick Quiz
Tip
Tip
Practice ON DELETE CASCADE ON UPDATE CASCADE in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
The cascade determines which style wins when multiple rules conflict
Common Mistake
Warning
A common mistake with ON DELETE CASCADE ON UPDATE CASCADE is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of ON DELETE CASCADE ON UPDATE CASCADE from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- CASCADE rules automate how child table rows respond when a parent row is deleted or updated.
- ON DELETE CASCADE - delete children when parent deleted
- ON DELETE SET NULL - set FK to NULL (FK column must allow NULL)
- ON DELETE SET DEFAULT - set FK to its DEFAULT value