Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Constraints and Data Types

SQL UNIQUE constraint

Reference for the SQL UNIQUE constraint: forbid duplicate values, composite unique keys, how NULLs are treated, adding it later and dialect differences.

A UNIQUE constraint guarantees that no two rows have the same value in a column, or the same combination of values across several columns. Use it for things that identify a row but are not its primary key: email addresses, product codes, usernames.

Unlike a primary key, a table may have many UNIQUE constraints, and the columns may usually contain NULL. The database enforces the rule with a unique index, which also speeds up lookups by that value.

SQL
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

Try it

SQL UNIQUE rejects duplicates (fails on purpose at the end) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL NULLs do not count as duplicates, composite uniqueness ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
-- column constraint
column_name data_type UNIQUE

-- table constraint (for several columns)
[CONSTRAINT constraint_name] UNIQUE (column_name [, ...])
Part Description
column_name The column or columns whose value (or combination) must be unique.
CONSTRAINT constraint_name Optional name, used in errors and when dropping the constraint.

Rules: for a composite constraint, only the whole combination must be unique, not each column on its own. A second row is rejected with an error such as "UNIQUE constraint failed".

Dialect differences

Dialect Notes
SQLite Any number of NULLs are allowed. Cannot add a constraint with ALTER TABLE; create a UNIQUE INDEX instead.
PostgreSQL Any number of NULLs by default. UNIQUE NULLS NOT DISTINCT (PostgreSQL 15) treats them as equal. Comparison is case-sensitive.
MySQL Any number of NULLs. Comparison follows the column collation, which is case-insensitive by default, so 'Ada' and 'ada' collide.
SQL Server Allows only one NULL in a unique column. A filtered unique index (WHERE col IS NOT NULL) works around it.
Oracle Several NULLs allowed in a single-column constraint, but a composite one with all columns NULL counts once.
SQL
-- PostgreSQL 15 and later
CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT UNIQUE NULLS NOT DISTINCT
);

-- SQL Server: unique but allow many NULLs
CREATE UNIQUE INDEX uq_users_phone ON users (phone) WHERE phone IS NOT NULL;

Usage notes

  • NULL is not equal to NULL, so under the standard rules UNIQUE lets rows with a missing value coexist. Add NOT NULL if every row must have a value.
  • Case and spaces. '[email protected]' and '[email protected]' are different strings in most databases. Normalize with LOWER() before saving, or use an expression index or a case-insensitive collation.
  • Partial uniqueness ("one active subscription per user") needs a partial unique index: CREATE UNIQUE INDEX ... ON t (user_id) WHERE active = 1 in SQLite and PostgreSQL.
  • Enforcement is by index. Dropping the index in some systems drops the constraint, and the index uses disk space and slows writes slightly.
  • Race conditions. Checking with SELECT and then inserting is not safe under concurrency. Insert and handle the unique-violation error, or use upsert.
  • A foreign key can reference a UNIQUE column, not just a primary key.