SQL Constraints

Constraints are rules the database enforces on column values. They prevent bad data from being inserted, maintaining integrity even when application code has bugs. The database is the last line of defense.

PRIMARY KEY

A primary key uniquely identifies each row in a table. It must be:

Unique — no two rows can have the same value
NOT NULL — every row must have a value

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,  -- single-column PK
    name VARCHAR(255) NOT NULL
);

-- Composite primary key (multiple columns together are unique)
CREATE TABLE order_items (
    order_id    INTEGER,
    product_id  INTEGER,
    quantity    INTEGER,
    PRIMARY KEY (order_id, product_id)
);

Choosing a primary key:

For most tables: SERIAL or BIGSERIAL (auto-increment integer)
For distributed systems or public APIs: UUID
Avoid using meaningful data (SSN, email) as PK — those can change

FOREIGN KEY

A foreign key enforces referential integrity — a value in the FK column must exist as a primary key in the referenced table:

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    amount      NUMERIC(10, 2)
);

This prevents:

Inserting an order for a customer that doesn’t exist
Deleting a customer who still has orders (unless CASCADE is specified)

Cascade behavior:

customer_id INTEGER REFERENCES customers(customer_id)
    ON DELETE CASCADE    -- delete orders when customer is deleted
    ON DELETE SET NULL   -- set FK to NULL when customer is deleted
    ON DELETE RESTRICT   -- prevent customer deletion if orders exist (default)
    ON UPDATE CASCADE    -- update FK when customer_id changes

Add FK to an existing table:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

Always index FK columns — the database uses the FK column in JOIN operations, and without an index it must scan the entire child table:

CREATE INDEX idx_orders_customer ON orders(customer_id);

NOT NULL

Prevents a column from containing NULL — the value must always be provided:

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name       VARCHAR(255) NOT NULL,    -- required
    sku        VARCHAR(50)  NOT NULL,    -- required
    price      NUMERIC(10, 2) NOT NULL, -- required
    description TEXT                     -- optional (NULLable)
);

Add NOT NULL to an existing column:

-- First populate any NULLs
UPDATE products SET description = '' WHERE description IS NULL;
-- Then add the constraint
ALTER TABLE products ALTER COLUMN description SET NOT NULL;

UNIQUE

Ensures all values in a column (or combination of columns) are distinct:

CREATE TABLE users (
    user_id  SERIAL PRIMARY KEY,
    email    VARCHAR(320) UNIQUE NOT NULL,  -- column-level UNIQUE
    username VARCHAR(50)  UNIQUE NOT NULL
);

-- Multi-column UNIQUE (the combination must be unique)
CREATE TABLE product_tags (
    product_id INTEGER REFERENCES products(product_id),
    tag_id     INTEGER REFERENCES tags(tag_id),
    UNIQUE (product_id, tag_id)   -- can't add the same tag twice to one product
);

NULL and UNIQUE: Multiple NULLs are allowed in a UNIQUE column (NULLs are considered distinct from each other in SQL standard; MySQL is an exception).

Add UNIQUE to existing column:

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

CHECK

A custom condition that every row must satisfy:

CREATE TABLE products (
    product_id  SERIAL PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    price       NUMERIC(10, 2) CHECK (price >= 0),
    discount    NUMERIC(5, 2) CHECK (discount BETWEEN 0 AND 100),
    weight_kg   NUMERIC(8, 3) CHECK (weight_kg > 0),
    status      VARCHAR(20)   CHECK (status IN ('active', 'inactive', 'archived'))
);

Multi-column CHECK:

CREATE TABLE appointments (
    appointment_id SERIAL PRIMARY KEY,
    start_time     TIMESTAMPTZ NOT NULL,
    end_time       TIMESTAMPTZ NOT NULL,
    CONSTRAINT chk_time_order CHECK (end_time > start_time)
);

Add CHECK to an existing table:

ALTER TABLE products
ADD CONSTRAINT chk_price_positive CHECK (price >= 0);

DEFAULT

Provides a value when no value is specified on INSERT:

CREATE TABLE orders (
    order_id   SERIAL PRIMARY KEY,
    status     VARCHAR(20)    DEFAULT 'pending',
    is_active  BOOLEAN        DEFAULT TRUE,
    created_at TIMESTAMPTZ    DEFAULT NOW(),
    quantity   INTEGER        DEFAULT 1
);

-- Inserting without specifying status, is_active, created_at, quantity:
INSERT INTO orders (customer_id, amount)
VALUES (101, 149.99);
-- status = 'pending', is_active = TRUE, created_at = current time, quantity = 1

Modify the DEFAULT on an existing column:

ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'new';
ALTER TABLE orders ALTER COLUMN status DROP DEFAULT;  -- remove the default

Summary Table

Constraint    | What it prevents / provides
──────────────────────────────────────────────────────────────────
PRIMARY KEY   | No duplicates, no NULLs; uniquely identifies each row
FOREIGN KEY   | References must exist in the parent table; no orphaned records
NOT NULL      | Column must always have a value; NULLs rejected
UNIQUE        | No duplicate values (NULLs allowed)
CHECK         | Custom rule: value must satisfy a boolean expression
DEFAULT       | Provides a fallback value when INSERT omits the column

Constraint Naming

Always name constraints explicitly — auto-generated names are opaque and make migration scripts harder:

CONSTRAINT pk_customers PRIMARY KEY (customer_id)
CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
CONSTRAINT uq_users_email UNIQUE (email)
CONSTRAINT chk_price_non_negative CHECK (price >= 0)

Named constraints are easier to drop, reference in error messages, and identify in migration logs.

Viewing Constraints

PostgreSQL:

SELECT
    constraint_name,
    constraint_type,
    table_name
FROM information_schema.table_constraints
WHERE table_schema = 'public'
ORDER BY table_name, constraint_type;