Module 27 — Constraints Deep Dive

1. Foreign Key Actions

What it is: Foreign key actions define what happens to dependent rows when the referenced row is deleted or updated. The default (RESTRICT) blocks deletion, but you can configure CASCADE (delete children), SET NULL (orphan children), or SET DEFAULT.

Why we use it: Without actions, deleting a user with posts would fail. CASCADE automatically cleans up dependent data, SET NULL preserves children but removes the link, and RESTRICT prevents accidental data loss.

When we use it: CASCADE for data that should be deleted together (user → sessions), SET NULL for data that should survive independently (user → posts), RESTRICT for critical relationships that should never be broken.

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);

CASCADE — Delete Children Too

What it is: ON DELETE CASCADE automatically deletes all dependent rows when the parent row is deleted. Deleting a user cascades to delete all their posts, comments, and sessions.

Why we use it: When child data has no meaning without the parent — sessions, tokens, temporary data. It ensures clean deletion without orphaned records.

When we use it: For data that's tightly coupled to the parent — user sessions, cache entries, temporary data, or data that would be meaningless without the parent.

-- When a user is deleted, delete all their posts
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);
 
DELETE FROM users WHERE id = 1;  -- Also deletes all posts by user 1

SET NULL — Orphan Children

What it is: ON DELETE SET NULL sets the foreign key to NULL when the parent row is deleted. The child row survives but loses its connection to the parent.

Why we use it: When child data should survive independently — posts should remain even if the author's account is deleted. The post becomes "orphaned" but still accessible.

When we use it: For data that has value without the parent — posts, comments, orders, or any data that should be preserved for historical/audit purposes.

-- When a user is deleted, set post.user_id to NULL
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL
);

RESTRICT — Block Deletion (Default)

What it is: ON DELETE RESTRICT (the default) prevents deletion of a parent row if any child rows reference it. The delete fails with an error.

Why we use it: When you want to prevent accidental data loss — you shouldn't delete a user who still has orders, or a category that still has products. RESTRICT forces you to handle dependencies first.

When we use it: For critical relationships where deletion should be explicit — users with orders, products with inventory, categories with products.

-- Block deletion if posts exist
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE RESTRICT
);
 
DELETE FROM users WHERE id = 1;  -- ERROR: posts reference this user

2. Composite Primary Keys

What it is: A composite primary key uses two or more columns to uniquely identify a row. It's commonly used in junction tables that link two entities in a many-to-many relationship.

Why we use it: In a junction table (like enrollments linking students and courses), the combination of student_id + course_id is the natural unique identifier. A composite key enforces this uniqueness.

When we use it: In junction/bridge tables for many-to-many relationships, when the natural key is a combination of columns, or when you need to prevent duplicate relationships.

-- Junction table for many-to-many relationship
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(id) ON DELETE CASCADE,
    course_id INTEGER REFERENCES courses(id) ON DELETE CASCADE,
    enrolled_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (student_id, course_id)
);

3. Deferred Constraints

What it is: By default, constraints are checked after each statement. Deferred constraints wait until the transaction commits (COMMIT) before checking. This allows operations that would temporarily violate constraints within the transaction.

Why we use it: Some operations require inserting data in a specific order that would violate foreign keys temporarily — like inserting an order_item before the order exists. Deferred constraints allow this within a transaction.

When we use it: During complex data migrations, when inserting related data in non-standard order, or when circular references exist between tables.

-- Create a deferred constraint
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) DEFERRABLE INITIALLY DEFERRED,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL
);
 
-- Now you can insert an order_item before the order exists
BEGIN;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (999, 1, 

4. Constraint Naming Conventions

What it is: Constraint naming conventions assign human-readable names to constraints using prefixes like pk_, fk_, uq_, chk_. Without explicit names, PostgreSQL auto-generates cryptic names like users_email_key.

Why we use it: Named constraints are easier to debug, drop, and reference in error messages. When a constraint violation occurs, uq_users_email tells you exactly what failed, while users_email_key is less clear.

When we use it: Always. Every constraint should have an explicit name, especially in production databases.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,                              -- pk_users
    email VARCHAR(255) CONSTRAINT uq_users_email UNIQUE, -- uq_users_email
    age INTEGER CONSTRAINT chk_users_age CHECK (age >= 13) -- chk_users_age
);
 
CREATE TABLE posts

5. Adding and Dropping Constraints

What it is: You can add or drop constraints after table creation using ALTER TABLE. This is essential for evolving your schema as business rules change.

Why we use it: Constraints enforce data integrity. You might realize a column should be unique, or that a foreign key is needed. Adding constraints after creation lets you tighten rules without losing data.

When we use it: When adding new validation rules, when linking tables that weren't previously related, or when tightening security requirements.

-- Add a constraint
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);
 
-- Drop a constraint
ALTER TABLE users DROP CONSTRAINT uq_users_email;
 
-- Add a foreign key
ALTER TABLE posts ADD CONSTRAINT fk_posts_users
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

Common Mistakes to Avoid

1. Using CASCADE without thinking — Deleting a user cascades to all their data
2. Not naming constraints — Auto-generated names are hard to debug
3. Forgetting ON DELETE — Default RESTRICT blocks deletions unexpectedly

Practice Exercise

1. Create a students table and a courses table
2. Create a junction table enrollments with a composite primary key
3. Add ON DELETE CASCADE to both foreign keys
4. Insert sample data, then delete a student and verify enrollments are cascaded
5. Add a CHECK constraint that ensures enrolled_at is not in the future