1️⃣UNIQUE

No duplicate key tuples (with a NULL twist)

A UNIQUE constraint (or unique index) forbids two rows from sharing the same combination of values in the indexed columns-when those values are non-NULL. In standard SQL, NULL is not equal to NULL, so multiple rows with NULL in a unique column are usually allowed. Use UNIQUE … NULLS NOT DISTINCT (PostgreSQL 15+) or partial/filtered unique indexes when “only one unknown” is a real business rule.

SQL

Composite alternate key

CREATE TABLE core.protocol_versions (
  protocol_id INT NOT NULL,
  version INT NOT NULL,
  published_on DATE NOT NULL,
  UNIQUE (protocol_id, version)
);

✓Illustrative result set

status
CREATE TABLE

SQL

Violation on second insert of same tuple

INSERT INTO core.protocol_versions VALUES (1, 1, '2026-01-01');
INSERT INTO core.protocol_versions VALUES (1, 1, '2026-02-01');

✓Illustrative result set

error
ERROR: duplicate key value violates unique constraint

Case-sensitive email “duplicates”

User@lab.org and user@lab.org both satisfy UNIQUE on raw text, two accounts, one human.

Normalize (lower) in a generated column or app layer + unique on normalized form

Store canonical lowercase in email_canonical with a unique constraint, or use citext on Postgres with eyes open to collation rules.

🎯Partial / filtered UNIQUE

Uniqueness only where it matters, e.g. one active promotion per SKU.

Soft-deleted rows often need uniqueness among active rows only. Express that with a partial unique index (WHERE deleted_at IS NULL) so history does not block new inserts.

vs PRIMARY KEY

Tables may have many UNIQUE alternate keys (MRN, accession) but at most one PRIMARY KEY, the default target for foreign keys unless you reference a UNIQUE constraint explicitly.

Wide keys

Unique constraints on long TEXT columns bloat indexes; prefer surrogate keys plus UNIQUE on a hash or normalized short key when needed.

⚡ Advanced / dialect note

Unique constraints are implemented as indexes, review migration plans for write amplification on hot insert paths the same way you would for a manually created index.

NULL policy

Before relying on “UNIQUE prevents duplicates,” write down your engine’s NULL behavior and add a test row set that includes multiple NULLs if that state is legal.

TL;DR

A UNIQUE constraint forbids duplicate values for a column or column tuple (but often allows multiple NULLs in SQL, three-valued logic treats each NULL as distinct unknown). Unlike primary keys, tables may have many unique constraints for alternate business keys (email, external id). Unique constraints are usually enforced with an index, dropping or deferring them has the same performance implications as other indexes.