Table of Contents
- 1 The Problem: Default Behavior Causes Unexpected Issues
- 2 Understanding Foreign Key Actions
- 3 Real-World Examples
- 3.1 Example 1: Blog Platform (CASCADE for Owned Content)
- 3.2 Example 2: E-commerce (SET NULL for Historical Records)
- 3.3 Example 3: Organization Hierarchy (SET DEFAULT for Manager Changes)
- 3.4 Example 4: Product Catalog (RESTRICT for Safety)
- 3.5 Example 5: Audit Logging (NO ACTION DEFERRABLE)
- 3.6 Example 6: Mixed Actions (Realistic Scenario)
- 4 Advanced Patterns
- 5 ON UPDATE Actions
- 6 Querying Foreign Key Information
- 7 Common Pitfalls
- 8 Decision Matrix
- 9 Testing Foreign Key Actions
- 10 Performance Considerations
- 11 Summary
- 12 Further Reading
The Problem: Default Behavior Causes Unexpected Issues
You’re building a blog platform with users and posts. The naive approach uses a simple foreign key:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT UNIQUE NOT NULL,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES users(id), -- Simple FK, no action specified
title TEXT NOT NULL,
content TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
What happens when you try to delete a user?
-- Alice creates some posts INSERT INTO users VALUES (1, 'alice', '[email protected]'); INSERT INTO posts VALUES (101, 1, 'My First Post', 'Hello world!'); INSERT INTO posts VALUES (102, 1, 'Second Post', 'More content'); -- Try to delete Alice DELETE FROM users WHERE id = 1; -- ✗ ERROR: update or delete on table "users" violates foreign key constraint "posts_user_id_fkey" on table "posts" -- DETAIL: Key (id)=(1) is still referenced from table "posts".
The default behavior is ON DELETE RESTRICT – prevents deletion if child rows exist.
Problem Scenarios with Default Behavior
Scenario 1: Can’t Delete Inactive Users
-- User account was compromised, needs deletion DELETE FROM users WHERE username = 'compromised_account'; -- ✗ ERROR: Still has posts -- Workaround: Manually delete all child records first DELETE FROM posts WHERE user_id = (SELECT id FROM users WHERE username = 'compromised_account'); DELETE FROM users WHERE username = 'compromised_account'; -- Tedious and error-prone with multiple child tables!
Scenario 2: Can’t Clean Up Test Data
-- Development: Want to delete test users DELETE FROM users WHERE email LIKE '%@test.com'; -- ✗ ERROR: They all have posts -- Must manually clean up each related table -- What if you forget one? Inconsistent test data!
Scenario 3: Orphaned Records After Manual Cleanup
-- Developer manually deletes posts DELETE FROM posts WHERE user_id = 1; -- Then deletes user DELETE FROM users WHERE id = 1; -- ✓ Success -- But wait, what about: -- - comments on those posts? -- - likes on those posts? -- - notifications about those posts? -- All now orphaned with user_id = 1 that doesn't exist!
Understanding Foreign Key Actions
PostgreSQL provides five different actions for handling deletions and updates:
CREATE TABLE child_table (
id INTEGER PRIMARY KEY,
parent_id INTEGER REFERENCES parent_table(id)
ON DELETE { RESTRICT | CASCADE | SET NULL | SET DEFAULT | NO ACTION }
ON UPDATE { RESTRICT | CASCADE | SET NULL | SET DEFAULT | NO ACTION }
);
1. RESTRICT (Default)
Prevents deletion/update if child rows exist.
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE RESTRICT
);
DELETE FROM users WHERE id = 1;
-- ✗ ERROR: Cannot delete user while posts exist
When to use:
- When child records must have a valid parent
- When deletion should be explicit and controlled
- Example: Can’t delete a product category while products exist
2. CASCADE
Automatically deletes/updates child rows when parent is deleted/updated.
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);
DELETE FROM users WHERE id = 1;
-- ✓ Success: User AND all their posts are deleted automatically
When to use:
- Child records are owned by the parent (composition relationship)
- Child records have no meaning without the parent
- Example: Deleting a user deletes their posts
3. SET NULL
Sets foreign key to NULL when parent is deleted/updated.
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL
);
DELETE FROM users WHERE id = 1;
-- ✓ Success: User deleted, posts.user_id becomes NULL
-- Posts still exist but are now "anonymous"
When to use:
- Child records should survive parent deletion
- NULL has semantic meaning (e.g., “author deleted”)
- Example: Comments by deleted users show as “[deleted]”
4. SET DEFAULT
Sets foreign key to a default value when parent is deleted/updated.
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE SET DEFAULT DEFAULT 999
);
-- Must have a user with id=999 (e.g., "System User")
INSERT INTO users VALUES (999, 'system', '[email protected]');
DELETE FROM users WHERE id = 1;
-- ✓ Success: Posts now assigned to user_id=999
When to use:
- Want to reassign orphaned records to a default owner
- Example: Deleted manager’s employees reassigned to department head
5. NO ACTION
Similar to RESTRICT but can be deferred until end of transaction.
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE NO ACTION DEFERRABLE
);
-- With deferrable constraints, can reorder operations in transaction
BEGIN;
DELETE FROM users WHERE id = 1;
DELETE FROM posts WHERE user_id = 1;
COMMIT;
-- ✓ Success: Check happens at commit time
When to use:
- Complex transactions with circular dependencies
- Need fine control over constraint checking timing
- Less common than other actions
Real-World Examples
Example 1: Blog Platform (CASCADE for Owned Content)
CREATE TABLE users (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
username TEXT UNIQUE NOT NULL,
email TEXT UNIQUE NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE posts (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title TEXT NOT NULL,
content TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE comments (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
content TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE likes (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE(post_id, user_id)
);
Test cascade deletion:
-- Alice creates content
INSERT INTO users (username, email) VALUES ('alice', '[email protected]') RETURNING id;
-- Returns id: 1
INSERT INTO posts (user_id, title, content) VALUES (1, 'Hello World', 'My first post');
-- post id: 1
INSERT INTO comments (post_id, user_id, content) VALUES (1, 1, 'First comment!');
INSERT INTO likes (post_id, user_id) VALUES (1, 1);
-- Check data
SELECT COUNT(*) FROM posts WHERE user_id = 1; -- 1
SELECT COUNT(*) FROM comments WHERE user_id = 1; -- 1
SELECT COUNT(*) FROM likes WHERE user_id = 1; -- 1
-- Delete Alice
DELETE FROM users WHERE id = 1;
-- ✓ Success
-- Everything cascaded
SELECT COUNT(*) FROM posts WHERE user_id = 1; -- 0
SELECT COUNT(*) FROM comments WHERE user_id = 1; -- 0
SELECT COUNT(*) FROM likes WHERE user_id = 1; -- 0
Cascade chain:
DELETE users(1)
→ CASCADE deletes posts(1)
→ CASCADE deletes comments on post(1)
→ CASCADE deletes likes on post(1)
→ CASCADE deletes comments by user(1)
→ CASCADE deletes likes by user(1)
Example 2: E-commerce (SET NULL for Historical Records)
CREATE TABLE customers (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
total NUMERIC(10, 2) NOT NULL,
status TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE order_items (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_name TEXT NOT NULL,
quantity INTEGER NOT NULL,
price NUMERIC(10, 2) NOT NULL
);
Test SET NULL:
-- Customer makes an order
INSERT INTO customers (name, email) VALUES ('Bob Smith', '[email protected]') RETURNING id;
-- Returns id: 1
INSERT INTO orders (customer_id, total, status) VALUES (1, 199.99, 'completed') RETURNING id;
-- Returns id: 1
INSERT INTO order_items (order_id, product_name, quantity, price)
VALUES (1, 'Widget', 2, 99.99);
-- Customer requests account deletion (GDPR)
DELETE FROM customers WHERE id = 1;
-- ✓ Success
-- Order persists for accounting, but customer_id is NULL
SELECT id, customer_id, total, status FROM orders WHERE id = 1;
-- id | customer_id | total | status
-- 1 | NULL | 199.99 | completed
-- Order items still exist (cascade from order, not customer)
SELECT * FROM order_items WHERE order_id = 1;
-- Widget order still there
Why SET NULL here?
- Legal requirement: Keep order history for 7+ years
- Privacy requirement: Allow customer data deletion
- Solution: Keep orders but anonymize them
Example 3: Organization Hierarchy (SET DEFAULT for Manager Changes)
-- Create default "unassigned" user
CREATE TABLE employees (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
manager_id INTEGER REFERENCES employees(id)
ON DELETE SET DEFAULT
DEFAULT 1, -- CEO or HR default
department TEXT NOT NULL
);
-- Insert CEO/default manager
INSERT INTO employees (id, name, email, department)
VALUES (1, 'System Account', '[email protected]', 'HR');
Test SET DEFAULT:
-- Alice manages Bob and Charlie
INSERT INTO employees (name, email, manager_id, department)
VALUES
('Alice', '[email protected]', 1, 'Engineering') RETURNING id; -- id: 2
INSERT INTO employees (name, email, manager_id, department)
VALUES
('Bob', '[email protected]', 2, 'Engineering'), -- id: 3
('Charlie', '[email protected]', 2, 'Engineering'); -- id: 4
-- Alice leaves the company
DELETE FROM employees WHERE id = 2;
-- ✓ Success
-- Bob and Charlie now report to default (id=1)
SELECT name, manager_id FROM employees WHERE id IN (3, 4);
-- name | manager_id
-- Bob | 1
-- Charlie | 1
Example 4: Product Catalog (RESTRICT for Safety)
CREATE TABLE categories (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name TEXT UNIQUE NOT NULL
);
CREATE TABLE products (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
category_id INTEGER NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
name TEXT NOT NULL,
price NUMERIC(10, 2) NOT NULL
);
Test RESTRICT:
INSERT INTO categories (name) VALUES ('Electronics') RETURNING id; -- id: 1
INSERT INTO products (category_id, name, price) VALUES (1, 'Laptop', 999.99);
-- Try to delete category with products
DELETE FROM categories WHERE id = 1;
-- ✗ ERROR: update or delete on table "categories" violates foreign key constraint
-- DETAIL: Key (id)=(1) is still referenced from table "products".
-- Must explicitly delete products first
DELETE FROM products WHERE category_id = 1;
DELETE FROM categories WHERE id = 1;
-- ✓ Success
Why RESTRICT here?
- Accidentally deleting a category would orphan hundreds of products
- Requires explicit, conscious decision
- Prevents cascading disasters
Example 5: Audit Logging (NO ACTION DEFERRABLE)
CREATE TABLE users (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
username TEXT UNIQUE NOT NULL
);
CREATE TABLE sessions (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id)
ON DELETE NO ACTION
DEFERRABLE INITIALLY DEFERRED,
started_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE audit_log (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
session_id INTEGER NOT NULL REFERENCES sessions(id)
ON DELETE NO ACTION
DEFERRABLE INITIALLY DEFERRED,
action TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
Test DEFERRABLE:
-- Create user and session
INSERT INTO users (username) VALUES ('alice') RETURNING id; -- id: 1
INSERT INTO sessions (user_id) VALUES (1) RETURNING id; -- id: 1
INSERT INTO audit_log (session_id, action) VALUES (1, 'login');
-- Clean up in transaction (order doesn't matter with DEFERRABLE)
BEGIN;
DELETE FROM users WHERE id = 1;
DELETE FROM audit_log WHERE session_id = 1;
DELETE FROM sessions WHERE id = 1;
COMMIT;
-- ✓ Success: Constraints checked at COMMIT time
Example 6: Mixed Actions (Realistic Scenario)
CREATE TABLE tenants (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE users (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
tenant_id INTEGER NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
username TEXT NOT NULL,
created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
UNIQUE(tenant_id, username)
);
CREATE TABLE documents (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
tenant_id INTEGER NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
owner_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
title TEXT NOT NULL
);
Business rules:
- Delete tenant → CASCADE delete all users and documents
- Delete user → SET NULL on created_by and owner_id (keep audit trail)
Test:
-- Create tenant
INSERT INTO tenants (name) VALUES ('Acme Corp') RETURNING id; -- id: 1
-- Alice creates account
INSERT INTO users (tenant_id, username) VALUES (1, 'alice') RETURNING id; -- id: 1
-- Alice creates Bob
INSERT INTO users (tenant_id, username, created_by) VALUES (1, 'bob', 1) RETURNING id; -- id: 2
-- Bob creates document
INSERT INTO documents (tenant_id, owner_id, title) VALUES (1, 2, 'Report') RETURNING id; -- id: 1
-- Alice leaves
DELETE FROM users WHERE id = 1;
-- ✓ Success
-- Bob's created_by is now NULL (Alice deleted)
SELECT username, created_by FROM users WHERE id = 2;
-- bob | NULL
-- Delete entire tenant
DELETE FROM tenants WHERE id = 1;
-- ✓ Success
-- Everything cascaded
SELECT COUNT(*) FROM users WHERE tenant_id = 1; -- 0
SELECT COUNT(*) FROM documents WHERE tenant_id = 1; -- 0
Advanced Patterns
Pattern 1: Soft Deletes Instead of CASCADE
Sometimes you want CASCADE-like behavior but with soft deletes:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL,
deleted_at TIMESTAMPTZ
);
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE RESTRICT,
title TEXT NOT NULL,
deleted_at TIMESTAMPTZ
);
-- Trigger to soft-delete posts when user is soft-deleted
CREATE FUNCTION cascade_soft_delete_posts() RETURNS TRIGGER AS $$
BEGIN
IF NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NULL THEN
UPDATE posts
SET deleted_at = NEW.deleted_at
WHERE user_id = NEW.id AND deleted_at IS NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER soft_delete_user_posts
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION cascade_soft_delete_posts();
Test:
INSERT INTO users VALUES (1, 'alice', NULL); INSERT INTO posts VALUES (1, 1, 'Post', NULL); -- Soft delete user UPDATE users SET deleted_at = NOW() WHERE id = 1; -- Post automatically soft-deleted SELECT title, deleted_at IS NOT NULL AS is_deleted FROM posts WHERE id = 1; -- Post | true
Pattern 2: Conditional CASCADE
Only cascade delete for certain statuses:
CREATE TABLE projects (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE tasks (
id INTEGER PRIMARY KEY,
project_id INTEGER REFERENCES projects(id) ON DELETE RESTRICT,
title TEXT NOT NULL,
status TEXT NOT NULL
);
-- Trigger to only allow deletion if all tasks are completed
CREATE FUNCTION check_project_deletable() RETURNS TRIGGER AS $$
BEGIN
IF EXISTS (
SELECT 1 FROM tasks
WHERE project_id = OLD.id
AND status != 'completed'
) THEN
RAISE EXCEPTION 'Cannot delete project with incomplete tasks';
END IF;
-- Delete completed tasks
DELETE FROM tasks WHERE project_id = OLD.id;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER before_delete_project
BEFORE DELETE ON projects
FOR EACH ROW
EXECUTE FUNCTION check_project_deletable();
Test:
INSERT INTO projects VALUES (1, 'Website'); INSERT INTO tasks VALUES (1, 1, 'Design', 'in_progress'); INSERT INTO tasks VALUES (2, 1, 'Code', 'completed'); -- Try to delete project with incomplete tasks DELETE FROM projects WHERE id = 1; -- ✗ ERROR: Cannot delete project with incomplete tasks -- Complete all tasks UPDATE tasks SET status = 'completed' WHERE id = 1; -- Now deletion works DELETE FROM projects WHERE id = 1; -- ✓ Success: Trigger deleted all completed tasks
Pattern 3: Logged CASCADE
Track what gets cascade deleted:
CREATE TABLE deletion_log (
id SERIAL PRIMARY KEY,
table_name TEXT NOT NULL,
record_id INTEGER NOT NULL,
deleted_by TEXT,
deleted_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE FUNCTION log_post_deletion() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO deletion_log (table_name, record_id, deleted_by)
VALUES ('posts', OLD.id, current_user);
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER log_post_deletes
BEFORE DELETE ON posts
FOR EACH ROW
EXECUTE FUNCTION log_post_deletion();
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL
);
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
title TEXT NOT NULL
);
Test:
INSERT INTO users VALUES (1, 'alice'); INSERT INTO posts VALUES (1, 1, 'First'), (2, 1, 'Second'); -- Delete user (cascades to posts) DELETE FROM users WHERE id = 1; -- Check deletion log SELECT * FROM deletion_log; -- id | table_name | record_id | deleted_by | deleted_at -- 1 | posts | 1 | postgres | 2024-03-15 ... -- 2 | posts | 2 | postgres | 2024-03-15 ...
ON UPDATE Actions
Foreign key actions also apply to UPDATEs of primary keys (less common):
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL
);
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES users(id)
ON UPDATE CASCADE -- Update posts when user id changes
ON DELETE CASCADE
);
INSERT INTO users VALUES (1, 'alice');
INSERT INTO posts VALUES (1, 1);
-- Change user's primary key
UPDATE users SET id = 999 WHERE id = 1;
-- Post's user_id automatically updated
SELECT user_id FROM posts WHERE id = 1;
-- 999
Common ON UPDATE actions:
ON UPDATE CASCADE -- Update child FK to match new parent PK ON UPDATE RESTRICT -- Prevent PK updates if children exist ON UPDATE SET NULL -- Set child FK to NULL when parent PK changes ON UPDATE SET DEFAULT -- Set child FK to default when parent PK changes ON UPDATE NO ACTION -- Like RESTRICT but deferrable
Best practice: Use natural keys or never update primary keys, making ON UPDATE actions unnecessary. When needed, CASCADE is most common.
Querying Foreign Key Information
List All Foreign Keys with Actions
SELECT
tc.table_name,
kcu.column_name,
ccu.table_name AS foreign_table,
ccu.column_name AS foreign_column,
rc.delete_rule,
rc.update_rule
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
ON ccu.constraint_name = tc.constraint_name
JOIN information_schema.referential_constraints rc
ON tc.constraint_name = rc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name, kcu.column_name;
-- table_name | column_name | foreign_table | foreign_column | delete_rule | update_rule
-- -----------+-------------+---------------+----------------+-------------+-------------
-- posts | user_id | users | id | CASCADE | NO ACTION
-- comments | post_id | posts | id | CASCADE | NO ACTION
-- comments | user_id | users | id | CASCADE | NO ACTION
Find Tables That Would Be Affected by Deletion
-- Function to show cascade chain
CREATE FUNCTION show_cascade_chain(table_name TEXT, record_id INTEGER)
RETURNS TABLE(level INTEGER, table_name TEXT, estimated_deletes BIGINT) AS $$
BEGIN
-- This is a simplified example
-- Real implementation would recursively follow FK relationships
RETURN QUERY
SELECT
1 as level,
'posts'::TEXT as table_name,
COUNT(*)::BIGINT as estimated_deletes
FROM posts
WHERE user_id = record_id
UNION ALL
SELECT
2 as level,
'comments'::TEXT as table_name,
COUNT(*)::BIGINT as estimated_deletes
FROM comments c
JOIN posts p ON c.post_id = p.id
WHERE p.user_id = record_id;
END;
$$ LANGUAGE plpgsql;
-- Show what would be deleted
SELECT * FROM show_cascade_chain('users', 1);
-- level | table_name | estimated_deletes
-- 1 | posts | 50
-- 2 | comments | 237
Common Pitfalls
Pitfall 1: Forgetting to Specify Action
-- ❌ Relies on default (RESTRICT)
CREATE TABLE posts (
user_id INTEGER REFERENCES users(id)
);
-- Later: Surprised when deletion fails
DELETE FROM users WHERE id = 1;
-- ✗ ERROR: constraint violation
Fix: Always explicitly specify:
-- ✅ Clear intent
CREATE TABLE posts (
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);
Pitfall 2: Using CASCADE Too Liberally
-- ❌ DANGEROUS: Everything cascades CREATE TABLE customers (id INTEGER PRIMARY KEY); CREATE TABLE orders (customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE); CREATE TABLE invoices (customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE); CREATE TABLE payments (customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE); -- Accidentally delete one customer... DELETE FROM customers WHERE id = 1; -- ...and lose entire financial history!
Fix: Use appropriate actions:
-- ✅ SAFER: Keep financial records CREATE TABLE customers (id INTEGER PRIMARY KEY); CREATE TABLE orders (customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL); CREATE TABLE invoices (customer_id INTEGER REFERENCES customers(id) ON DELETE RESTRICT); CREATE TABLE payments (customer_id INTEGER REFERENCES customers(id) ON DELETE RESTRICT);
Pitfall 3: SET NULL on NOT NULL Column
-- ❌ CONFLICT: Can't set NULL on NOT NULL column
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE SET NULL
);
DELETE FROM users WHERE id = 1;
-- ✗ ERROR: null value in column "user_id" violates not-null constraint
Fix: Either allow NULL or use different action:
-- Option 1: Allow NULL
CREATE TABLE posts (
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL
);
-- Option 2: Use CASCADE instead
CREATE TABLE posts (
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
);
Pitfall 4: SET DEFAULT Without Default Value
-- ❌ No default specified
CREATE TABLE posts (
user_id INTEGER REFERENCES users(id) ON DELETE SET DEFAULT
);
DELETE FROM users WHERE id = 1;
-- ✗ ERROR: null value in column "user_id" violates not-null constraint
Fix: Specify default value:
-- ✅ Correct
CREATE TABLE posts (
user_id INTEGER REFERENCES users(id) ON DELETE SET DEFAULT DEFAULT 999
);
-- Make sure default user exists
INSERT INTO users VALUES (999, 'System User', '[email protected]');
Pitfall 5: Circular CASCADE
-- ❌ DANGEROUS: Circular dependency
CREATE TABLE authors (
id INTEGER PRIMARY KEY,
latest_book_id INTEGER REFERENCES books(id) ON DELETE CASCADE
);
CREATE TABLE books (
id INTEGER PRIMARY KEY,
author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);
-- This can create deletion loops!
Fix: Use SET NULL for back-references:
-- ✅ SAFER
CREATE TABLE authors (
id INTEGER PRIMARY KEY,
latest_book_id INTEGER REFERENCES books(id) ON DELETE SET NULL
);
CREATE TABLE books (
id INTEGER PRIMARY KEY,
author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);
Decision Matrix
Choose the right action based on your use case:
| Scenario | Action | Reasoning |
|---|---|---|
| Blog post belongs to user | CASCADE | Posts are meaningless without author |
| Comment by deleted user | SET NULL | Keep comment, show as “[deleted]” |
| Product in category | RESTRICT | Don’t accidentally orphan products |
| Order by customer | SET NULL | Keep order history, anonymize customer |
| Employee’s manager | SET DEFAULT | Reassign to department head |
| Multi-tenant data | CASCADE | Tenant deletion removes all data |
| Audit log entries | RESTRICT | Never delete audit trails |
| Notification for post | CASCADE | Notifications irrelevant after post deleted |
| Invoice for customer | RESTRICT | Financial records must be explicit |
| Tag on article | CASCADE | Tags exist only within articles |
Testing Foreign Key Actions
Always test your foreign key actions before production:
-- Test script BEGIN; -- Create test data INSERT INTO users VALUES (999, 'test_user', '[email protected]'); INSERT INTO posts VALUES (999, 999, 'Test Post', 'Content'); INSERT INTO comments VALUES (999, 999, 999, 'Test Comment'); -- Test deletion DELETE FROM users WHERE id = 999; -- Verify expected behavior SELECT COUNT(*) FROM posts WHERE id = 999; -- Should be 0 with CASCADE SELECT COUNT(*) FROM comments WHERE id = 999; -- Should be 0 with CASCADE ROLLBACK; -- Don't commit test data
Performance Considerations
CASCADE Performance
-- Large cascade can be slow
-- Delete user with 10,000 posts, each with 100 comments = 1,000,000 rows deleted
-- Better: Delete in batches if possible
DELETE FROM comments WHERE post_id IN (
SELECT id FROM posts WHERE user_id = 1 LIMIT 1000
);
-- Repeat until all comments deleted, then posts, then user
Indexes on Foreign Keys
-- ⚠️ CASCADE is faster with index on FK CREATE INDEX idx_posts_user_id ON posts(user_id); CREATE INDEX idx_comments_post_id ON comments(post_id); CREATE INDEX idx_comments_user_id ON comments(user_id); -- Without indexes, CASCADE does sequential scans -- With indexes, CASCADE is much faster
Summary
Key Takeaways:
- Always explicitly specify ON DELETE and ON UPDATE actions
- CASCADE: Use for owned/composed relationships (user → posts)
- SET NULL: Use to preserve child records with nullable FK (orders → customer)
- RESTRICT: Use when deletion should be explicit (category → products)
- SET DEFAULT: Use to reassign orphans (employee → manager)
- Index foreign key columns for better CASCADE performance
- Don’t use CASCADE everywhere – think through each relationship
- Don’t forget NOT NULL compatibility with SET NULL
The Decision Framework:
- Ask: “If parent is deleted, should child be deleted too?”
- Yes → CASCADE
- No → Continue to next question
- Ask: “Should child record survive with NULL reference?”
- Yes → SET NULL (ensure column allows NULL)
- No → Continue to next question
- Ask: “Should child be reassigned to default parent?”
- Yes → SET DEFAULT (ensure default value exists)
- No → Use RESTRICT
The Bottom Line:
Foreign key actions are business logic encoded at the database level. They prevent orphaned records, simplify deletion logic, and make your schema’s relationships explicit and self-documenting. Choose actions that match your business rules, and your database will enforce data integrity automatically.
Further Reading
Next in the series: Item 7 – Use DEFERRABLE Constraints for Complex Multi-Table Operations
I build softwares that solve problems. I also love writing/documenting things I learn/want to learn.