Constraints No duplicates UNIQUE · composite · NULLS NOT DISTINCT

UNIQUE Constraint

Guarantee that values in a column — or combinations across columns — are distinct. Includes the NULL-is-distinct surprise and the PostgreSQL 15 NULLS NOT DISTINCT escape hatch.

What UNIQUE does

The UNIQUE constraint guarantees that every value in a column — or every combination of values in a set of columns — is distinct across the table. Try to insert or update a row whose value duplicates an existing one, and PostgreSQL refuses with a violation error.

This is the right tool for fields like email, username, order_number, or any natural identifier where two rows sharing the same value would be a bug.

Syntax

-- column-level
CREATE TABLE table_name (
    column1 data_type UNIQUE,
    column2 data_type
);

-- table-level (single or multi-column)
CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    CONSTRAINT uk_name UNIQUE (column1, column2)
);

Example 1 — column-level UNIQUE

SQL

CREATE TABLE managers (
    manager_id     INT CONSTRAINT mgr_uk UNIQUE,
    first_name     VARCHAR(20),
    last_name      VARCHAR(20),
    department_id  INT NOT NULL
);

-- First insert succeeds
INSERT INTO managers VALUES (100, 'John', 'Brown', 80);

-- Second insert with the same manager_id fails
INSERT INTO managers VALUES (100, 'Jane', 'Smith', 90);
-- ERROR: duplicate key value violates unique constraint "mgr_uk"

Example 2 — composite UNIQUE

A composite constraint says "the combination of these columns must be distinct." Each column on its own may repeat — only the pairing has to be unique.

SQL

CREATE TABLE enrollments (
    student_id  INT,
    course_id   INT,
    grade       CHAR(1),
    CONSTRAINT enrollments_uk UNIQUE (student_id, course_id)
);

-- Same student in different courses — fine
INSERT INTO enrollments VALUES (1, 101, 'A');
INSERT INTO enrollments VALUES (1, 102, 'B');

-- Same student in the same course — rejected
INSERT INTO enrollments VALUES (1, 101, 'A');
-- ERROR: duplicate key value violates unique constraint "enrollments_uk"

UNIQUE and NULL — the surprise

By default, a UNIQUE constraint treats every NULL as distinct from every other NULL. That means you can have many rows with NULL in the unique column without any of them conflicting.

SQL

CREATE TABLE users (
    id     SERIAL PRIMARY KEY,
    email  VARCHAR(100) UNIQUE
);

-- All three of these succeed even though email is NULL each time
INSERT INTO users (email) VALUES (NULL);
INSERT INTO users (email) VALUES (NULL);
INSERT INTO users (email) VALUES (NULL);

💡 PostgreSQL 15+: NULLS NOT DISTINCT. If you want NULLs to be treated as equal — so only one NULL row is allowed — use UNIQUE NULLS NOT DISTINCT:
email VARCHAR(100) UNIQUE NULLS NOT DISTINCT

Adding UNIQUE to an existing table

SQL

ALTER TABLE users
    ADD CONSTRAINT users_email_uk UNIQUE (email);

-- Drop later if needed
ALTER TABLE users
    DROP CONSTRAINT users_email_uk;

Adding a UNIQUE constraint scans the table first; if there are existing duplicates, the operation fails until you clean them up.

UNIQUE creates an index

Behind the scenes, PostgreSQL backs every UNIQUE constraint with a B-tree index. That gives you two benefits for free: fast lookups by that column, and the uniqueness check itself is cheap to perform on writes.

If you don't need the constraint but want the index for performance, you can create the index directly with CREATE UNIQUE INDEX — but the constraint form is more idiomatic when uniqueness is the actual goal.

Recap

UNIQUE rejects duplicate values across the table.
Single-column or composite — the latter only forbids duplicate combinations.
NULLs are considered distinct by default; use NULLS NOT DISTINCT (PostgreSQL 15+) to change that.
Adding the constraint requires that existing rows already conform.
A unique B-tree index is created automatically.