Chapter 11: Keys, Constraints, and Referential Integrity

Constraints are rules enforced by the database engine. No matter how buggy your application code is, constraints guarantee your data stays valid. They're your last line of defense.

Types of Keys

Primary Key

Uniquely identifies each row. Must be NOT NULL and UNIQUE. One per table.

-- Single-column PK
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

-- Composite PK (multiple columns together form the key)
CREATE TABLE enrollments (
    student_id INTEGER,
    course_id  INTEGER,
    grade      CHAR(1),
    PRIMARY KEY (student_id, course_id)
);

Foreign Key

References a primary key in another table. Enforces referential integrity — you can't have orphan records.

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total       NUMERIC(10,2),
    CONSTRAINT fk_customer
        FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE RESTRICT    -- prevent deleting customer with orders
        ON UPDATE CASCADE    -- if customer.id changes, update here too
);

Foreign Key Actions

Natural Key vs Surrogate Key

Constraint Types

CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    sku         VARCHAR(20) UNIQUE NOT NULL,        -- unique + required
    name        TEXT NOT NULL,                     -- required
    price       NUMERIC(10,2) CHECK (price > 0),  -- custom validation
    category    TEXT CHECK (category IN ('electronics', 'clothing', 'food')),
    weight_kg   NUMERIC CHECK (weight_kg >= 0),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- Multi-column constraint
    CONSTRAINT valid_dates CHECK (sale_end > sale_start)
);

Unique Constraints and Indexes

-- Single column unique
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

-- Multi-column unique (combination must be unique)
ALTER TABLE enrollments ADD CONSTRAINT unique_enrollment
    UNIQUE (student_id, course_id);

-- Partial unique (unique only among active users)
CREATE UNIQUE INDEX idx_active_email
    ON users (email) WHERE active = true;

Exclusion Constraints (PostgreSQL)

Prevent overlapping ranges — useful for scheduling:

-- No overlapping room bookings
CREATE TABLE bookings (
    room_id    INTEGER,
    time_range TSTZRANGE,
    EXCLUDE USING GIST (room_id WITH =, time_range WITH &&)
);
-- && means "overlaps" — this prevents double-booking a room

Referential Integrity in Practice