principlesqlpostgresqlModerate
Foreign key cascades: ON DELETE CASCADE vs application-level deletion
Viewed 0 times
foreign keyON DELETE CASCADESET NULLreferential integrityNOT VALIDVALIDATE CONSTRAINT
Error Messages
ERROR: insert or update on table "order_items" violates foreign key constraintERROR: update or delete on table "orders" violates foreign key constraint on table "order_items"Problem
Deleting a parent row fails with a foreign key violation because child rows still reference it, or developers silently break referential integrity by deleting in the wrong order without cascade configured.
Solution
Configure the appropriate cascade behavior on the foreign key:
-- Delete children automatically when parent is deleted:
ALTER TABLE order_items
ADD CONSTRAINT fk_order
FOREIGN KEY (order_id) REFERENCES orders(id)
ON DELETE CASCADE;
-- Set FK to NULL when parent is deleted (orphan preservation):
ALTER TABLE comments
ADD CONSTRAINT fk_post
FOREIGN KEY (post_id) REFERENCES posts(id)
ON DELETE SET NULL;
-- Block deletion if children exist (default behavior):
-- ON DELETE RESTRICT or ON DELETE NO ACTION (default)
-- Check existing FK constraints:
SELECT
tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table,
rc.delete_rule
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu USING (constraint_name)
JOIN information_schema.constraint_column_usage ccu USING (constraint_name)
JOIN information_schema.referential_constraints rc USING (constraint_name)
WHERE tc.constraint_type = 'FOREIGN KEY';
-- Delete children automatically when parent is deleted:
ALTER TABLE order_items
ADD CONSTRAINT fk_order
FOREIGN KEY (order_id) REFERENCES orders(id)
ON DELETE CASCADE;
-- Set FK to NULL when parent is deleted (orphan preservation):
ALTER TABLE comments
ADD CONSTRAINT fk_post
FOREIGN KEY (post_id) REFERENCES posts(id)
ON DELETE SET NULL;
-- Block deletion if children exist (default behavior):
-- ON DELETE RESTRICT or ON DELETE NO ACTION (default)
-- Check existing FK constraints:
SELECT
tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table,
rc.delete_rule
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu USING (constraint_name)
JOIN information_schema.constraint_column_usage ccu USING (constraint_name)
JOIN information_schema.referential_constraints rc USING (constraint_name)
WHERE tc.constraint_type = 'FOREIGN KEY';
Why
ON DELETE CASCADE is enforced by the database engine at the storage level, making deletion atomic and consistent. Application-level deletion order is fragile and breaks under concurrent writes or missed code paths.
Gotchas
- CASCADE can silently delete thousands of rows; always test with a BEGIN/ROLLBACK block first
- Adding a FK constraint to a large table acquires an AccessShareLock and scans for violations; use NOT VALID + VALIDATE CONSTRAINT to avoid locking
- Circular FK references require DEFERRABLE constraints
- ON DELETE SET NULL requires the FK column to be nullable
Code Snippets
Low-lock FK addition using NOT VALID + VALIDATE CONSTRAINT
-- Add FK without full table scan lock (large tables):
ALTER TABLE order_items
ADD CONSTRAINT fk_order
FOREIGN KEY (order_id) REFERENCES orders(id)
NOT VALID;
-- Validate later (weaker lock, can run concurrently):
ALTER TABLE order_items VALIDATE CONSTRAINT fk_order;Context
Database schema design and migrations involving parent-child table relationships
Revisions (0)
No revisions yet.