Advanced Constraints — CHECK, EXCLUSION and Partial Indexes

Basic constraints like NOT NULL and UNIQUE cover most needs, but PostgreSQL supports more powerful constraint types for complex business rules. Composite unique constraints enforce uniqueness across combinations of columns, multi-column CHECK constraints validate relationships between values in the same row, and deferred constraints relax when they are checked to allow complex multi-row inserts within a transaction. These advanced constraints push business rule enforcement into the database layer — the safest place to enforce them, since the database enforces them regardless of which application, service, or user modifies the data.

Composite and Conditional Unique Constraints #

-- ── Composite UNIQUE — uniqueness across multiple columns ────────────────────
CREATE TABLE post_tags (
    post_id BIGINT NOT NULL,
    tag_id  BIGINT NOT NULL,
    UNIQUE (post_id, tag_id)   -- a post can have the same tag only once
);
-- post_id=1 tag_id=5 ✓
-- post_id=1 tag_id=5 ← fails (duplicate pair)
-- post_id=2 tag_id=5 ✓ (different post)

-- Named UNIQUE constraint (clearer error messages)
CREATE TABLE users (
    id    BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    CONSTRAINT users_email_unique UNIQUE (email)
);

-- Partial unique index — unique only where condition is true
-- Only one active post per slug (archived posts with same slug OK)
CREATE UNIQUE INDEX idx_posts_slug_active
    ON posts(slug)
    WHERE status != 'archived';

-- One active password reset token per user
CREATE UNIQUE INDEX idx_users_active_reset_token
    ON password_resets(user_id)
    WHERE used_at IS NULL;

-- Unique email among non-deleted users (soft delete pattern)
CREATE UNIQUE INDEX idx_users_email_active
    ON users(email)
    WHERE deleted_at IS NULL;

CHECK Constraints #

-- Column-level CHECK
CREATE TABLE posts (
    id         BIGSERIAL PRIMARY KEY,
    title      TEXT NOT NULL
               CHECK (LENGTH(TRIM(title)) >= 3),
    view_count INTEGER NOT NULL DEFAULT 0
               CHECK (view_count >= 0),
    rating     NUMERIC(3,1)
               CHECK (rating IS NULL OR (rating >= 0 AND rating <= 5))
);

-- Table-level CHECK — can reference multiple columns
CREATE TABLE events (
    id         BIGSERIAL PRIMARY KEY,
    title      TEXT NOT NULL,
    starts_at  TIMESTAMPTZ NOT NULL,
    ends_at    TIMESTAMPTZ NOT NULL,
    CONSTRAINT events_valid_duration
        CHECK (ends_at > starts_at)   -- end must be after start
);

-- Named CHECK for clear error messages
CREATE TABLE products (
    id          BIGSERIAL PRIMARY KEY,
    price       NUMERIC(12, 2) NOT NULL,
    sale_price  NUMERIC(12, 2),
    CONSTRAINT products_sale_price_valid
        CHECK (sale_price IS NULL OR sale_price < price)   -- discount must be lower
);

-- CHECK with regex
CREATE TABLE users (
    id    BIGSERIAL PRIMARY KEY,
    slug  TEXT NOT NULL,
    CONSTRAINT users_slug_format
        CHECK (slug ~ '^[a-z0-9-]+$')   -- lowercase, digits, hyphens only
);

Deferred Constraints #

-- By default, constraints are checked immediately after each statement
-- DEFERRABLE constraints can be postponed until COMMIT

-- Use case: inserting rows that reference each other in a circular way
-- e.g., team has a captain (FK to players), players belong to team (FK to teams)

CREATE TABLE teams (
    id         BIGSERIAL PRIMARY KEY,
    name       TEXT NOT NULL,
    captain_id BIGINT   -- FK to players, but players table not yet defined
);

CREATE TABLE players (
    id      BIGSERIAL PRIMARY KEY,
    name    TEXT NOT NULL,
    team_id BIGINT NOT NULL REFERENCES teams(id)
);

-- Now add the FK from teams to players, deferrable:
ALTER TABLE teams ADD CONSTRAINT teams_captain_fk
    FOREIGN KEY (captain_id) REFERENCES players(id)
    DEFERRABLE INITIALLY DEFERRED;
-- DEFERRABLE: can be deferred
-- INITIALLY DEFERRED: deferred by default (checked at COMMIT, not at each statement)

-- Now we can insert both in the same transaction:
BEGIN;
INSERT INTO teams (name) VALUES ('Warriors') RETURNING id;   -- captain_id is NULL
INSERT INTO players (name, team_id) VALUES ('Alice', 1) RETURNING id;
UPDATE teams SET captain_id = 1 WHERE id = 1;
COMMIT;   -- FK checks happen here — all references now valid ✓

Common Mistakes #

Mistake 1 — Using standard UNIQUE instead of partial unique for soft delete #

❌ Wrong — cannot re-register with same email after soft delete:

email TEXT NOT NULL UNIQUE   -- deleted user's email blocks re-registration!

✅ Correct — partial unique index:

CREATE UNIQUE INDEX idx_users_email_active ON users(email) WHERE deleted_at IS NULL;   -- ✓

Mistake 2 — CHECK constraint that references another table (use FK instead) #

❌ Wrong — CHECK cannot query other tables:

CHECK (author_id IN (SELECT id FROM users WHERE is_active = TRUE))
-- ERROR: cannot use subquery in check constraint

✅ Correct — use a FK + application logic or trigger:

REFERENCES users(id)   -- FK ensures user exists; active check in application ✓

Mistake 3 — Multiple NULLs in a “unique” column without partial index #

❌ Wrong — expects only one NULL allowed:

token TEXT UNIQUE   -- allows multiple NULLs (each NULL ≠ each other NULL)

✅ Correct — if only one NULL is allowed, use a partial index:

CREATE UNIQUE INDEX idx_one_null_token ON table(token) WHERE token IS NOT NULL;
-- Plus app logic to prevent multiple NULLs, or NOT NULL constraint

Quick Reference #