UNIQUE Constraint

Ensure no two rows have the same value in a column (or combination of columns).

Overview

The UNIQUE constraint prevents duplicate values in a column or combination of columns. Unlike PRIMARY KEY, a UNIQUE column can contain NULLs and multiple NULLs are allowed because NULL is not considered equal to another NULL (one NULL ≠ another NULL in PostgreSQL's three-valued logic). Unique constraints can span multiple columns: a partial unique index can enforce uniqueness only for rows matching a condition. PostgreSQL automatically creates an index for each UNIQUE constraint, which also speeds up lookups on those columns.

Key Points

•UNIQUE: no two non-NULL rows can have the same value
•Multiple NULLs are allowed in a UNIQUE column
•Multi-column UNIQUE: UNIQUE (col1, col2) unique combination
•PostgreSQL creates an index automatically for UNIQUE constraints
•Partial unique index: CREATE UNIQUE INDEX ON t (col) WHERE condition

Examples

Single and multi-column UNIQUE

-- Single column
CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);

-- Multi-column unique (one active session per user per device)
CREATE TABLE sessions (
  user_id   INTEGER,
  device_id TEXT,
  UNIQUE (user_id, device_id)
);

Partial unique index

-- Only one active record per user at a time
CREATE UNIQUE INDEX ON subscriptions (user_id)
  WHERE status = 'active';

This allows multiple inactive subscriptions per user but enforces only one active.

UNIQUE Constraint

Overview

Key Points

Examples

Exercises

Knowledge Check

Can a UNIQUE column contain multiple NULL values?