How do UNIQUE constraints work and what are the edge cases?

Uniqueness can be declared at column level or as a table-level constraint over several columns. The database enforces it with a unique index, which also speeds up lookups. The constraint and the index are related but not identical concepts.

CREATE TABLE memberships (
  user_id BIGINT NOT NULL,
  team_id BIGINT NOT NULL,
  role TEXT NOT NULL,
  UNIQUE (user_id, team_id)
);

Edge cases

• PostgreSQL and many other engines allow multiple NULLs in a unique column by default
• PostgreSQL 15+ supports NULLS NOT DISTINCT when one NULL is enough
• A composite unique constraint only prevents duplicates of the full combination
• Concurrent inserts can both pass a pre-check, so rely on the constraint rather than SELECT then INSERT
• A unique index can also support query performance, not only integrity

If the business rule is at most one active record per user, a partial unique index can enforce it. For example, a unique index on user_id where revoked_at IS NULL prevents multiple active subscriptions without blocking historical rows.