UNIQUE, CHECK, and EXCLUDE Constraints

The database is your last line of defence

Constraints push business rules into the database itself. Validation in the application is helpful but optional — anyone can bypass it with a direct SQL connection or a buggy code path. Constraints are guaranteed. Every rule you push down is a rule you don't have to verify in 50 different places.

UNIQUE — no duplicates

Single-column or multi-column. Multi-column UNIQUE is the right answer for "a user can review a product at most once."

CHECK — arbitrary boolean rules

Any predicate that can be evaluated row-by-row: positive prices, valid statuses, sane percentages, lengths within limits. CHECK can reference other columns in the same row but not other rows.

EXCLUDE — overlap prevention

The most powerful and least-known constraint. EXCLUDE rejects rows whose values would conflict with existing rows under operators you choose. The classic case: prevent two reservations of the same room from overlapping in time.

Code

UNIQUE — single and multi-column·sql

CREATE TABLE users (
    id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email    TEXT UNIQUE NOT NULL,
    username TEXT UNIQUE NOT NULL
);

CREATE TABLE reviews (
    id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    INTEGER NOT NULL REFERENCES users(id),
    product_id INTEGER NOT NULL REFERENCES products(id),
    rating     INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    body       TEXT,
    UNIQUE (user_id, product_id)  -- one review per user per product
);

CHECK — business rules in schema·sql

CREATE TABLE orders (
    id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price    NUMERIC(10,2) NOT NULL CHECK (price >= 0),
    status   TEXT NOT NULL CHECK (status IN ('pending','paid','shipped','refunded')),
    discount NUMERIC(4,3) NOT NULL DEFAULT 0
                CHECK (discount BETWEEN 0 AND 1)
);

EXCLUDE — no overlapping room bookings·sql

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_bookings (
    id     INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room   TEXT NOT NULL,
    during TSRANGE NOT NULL,
    EXCLUDE USING gist (room WITH =, during WITH &&)
);

INSERT INTO room_bookings (room, during) VALUES
  ('A1', '[2026-05-10 10:00, 2026-05-10 11:00)');
INSERT INTO room_bookings (room, during) VALUES
  ('A1', '[2026-05-10 10:30, 2026-05-10 12:00)');
-- ERROR: conflicting key value violates exclusion constraint

UNIQUE, CHECK, and EXCLUDE Constraints

The database is your last line of defence

UNIQUE — no duplicates

CHECK — arbitrary boolean rules

EXCLUDE — overlap prevention

Code

External links

Exercise

Progress

Comments 0