SQL Academy · Lesson

UNIQUE Constraints and Composite Keys

Enforce uniqueness on single or multiple columns, understand NULLs in unique indexes, and use composite uniqueness for natural keys.

UNIQUE: No Duplicates Allowed

A UNIQUE constraint forbids two rows from sharing the same value in the constrained columns:

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  username VARCHAR(50) UNIQUE
);

A UNIQUE constraint is implemented with a unique B-tree index, so lookups by that column are fast.

-- This shows the implicit index:
SELECT indexname FROM pg_indexes WHERE tablename = 'users';