SQL Constraints — PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL

What constraints do

A constraint is a rule the database enforces on every write. It is the last line of defence against bad data — it catches what the application layer misses. Constraints run atomically with the write: the row is either committed with valid data or rejected entirely.

NOT NULL — the simplest constraint

CREATE TABLE users (
    id         BIGINT       PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    email      VARCHAR(254) NOT NULL,   -- must be provided
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    phone      VARCHAR(20)              -- intentionally nullable
);

Every column that should never be empty should be NOT NULL. This prevents the subtle bugs caused by NULL propagating through calculations and comparisons. Default-worthy columns (timestamps, counters, flags) should combine NOT NULL with a DEFAULT.

PRIMARY KEY — row identity

A primary key uniquely identifies each row. It implies NOT NULL and UNIQUE, and the database creates an index on it automatically.

-- Surrogate (generated) key
CREATE TABLE products (
    id           BIGINT       PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    sku          VARCHAR(50)  NOT NULL UNIQUE,
    product_name VARCHAR(200) NOT NULL
);

-- Composite primary key (junction table)
CREATE TABLE order_items (
    order_id    BIGINT NOT NULL REFERENCES orders(id),
    product_id  BIGINT NOT NULL REFERENCES products(id),
    quantity    INT    NOT NULL,
    unit_price  NUMERIC(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Use surrogate keys (auto-generated integers) for most tables. Use composite keys for junction/association tables where the combination of two foreign keys is the natural identity.

FOREIGN KEY — referential integrity

A foreign key ensures every value in the referencing column exists in the referenced table. It prevents orphaned rows (an order that references a non-existent customer).

CREATE TABLE orders (
    id          BIGINT       PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    customer_id BIGINT       NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    status      VARCHAR(20)  NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

ON DELETE / ON UPDATE actions:

-- Order items are removed when their order is deleted
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE

-- Soft relationship: customer deletion nullifies the field
FOREIGN KEY (assigned_agent_id) REFERENCES agents(id) ON DELETE SET NULL

Index foreign key columns — the database creates the index on the referenced column (primary key) automatically, but not on the referencing column. Without this index, every parent delete requires a full scan of the child table.

UNIQUE — prevent duplicates

UNIQUE ensures a column (or combination of columns) has no duplicate non-NULL values.

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

-- Composite unique: one active subscription per customer
CREATE TABLE subscriptions (
    id            BIGINT      PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    customer_id   BIGINT      NOT NULL REFERENCES customers(id),
    plan          VARCHAR(50) NOT NULL,
    status        VARCHAR(20) NOT NULL,
    CONSTRAINT subscriptions_active_unique UNIQUE (customer_id, status)
    -- Note: allows multiple rows if status differs (e.g., cancelled old ones)
);

-- Partial unique index (Postgres): only one active subscription per customer
CREATE UNIQUE INDEX ON subscriptions (customer_id) WHERE status = 'active';

A partial unique index is more precise than a full composite unique — it enforces uniqueness only within the subset of rows where the condition is true.

CHECK — custom domain rules

CHECK validates that a column value satisfies a boolean expression.

CREATE TABLE products (
    id         BIGINT         PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price > 0),
    stock_qty  INT            NOT NULL CHECK (stock_qty >= 0),
    status     VARCHAR(20)    NOT NULL
        CHECK (status IN ('active', 'discontinued', 'draft'))
);

-- Table-level check spanning multiple columns
CREATE TABLE discounts (
    id           BIGINT        PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    min_purchase NUMERIC(10,2) NOT NULL,
    max_purchase NUMERIC(10,2),
    CONSTRAINT discounts_range_valid CHECK (
        max_purchase IS NULL OR max_purchase > min_purchase
    )
);

Deferrable constraints (Postgres)

By default, constraint checks run immediately on each row write. DEFERRABLE INITIALLY DEFERRED moves the check to transaction commit time — useful when you need to insert rows in an order that temporarily violates a constraint.

-- Useful for circular references: departments and their managers
ALTER TABLE departments
    ADD CONSTRAINT departments_manager_fk
    FOREIGN KEY (manager_id) REFERENCES employees(id)
    DEFERRABLE INITIALLY DEFERRED;
-- Now you can INSERT a department and its manager in any order
-- within the same transaction; the FK is only checked at COMMIT.

Recap

Constraints are cheap insurance: NOT NULL eliminates NULL-propagation bugs; PRIMARY KEY guarantees row identity; FOREIGN KEY keeps references valid; UNIQUE prevents duplicate business keys; CHECK enforces domain rules that do not belong in application code. Index every foreign key column on the referencing side. Use partial unique indexes for "at most one active record" patterns. Define constraints in the schema, not just in application code — the database is the only layer that guarantees enforcement on every write path.