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.
CREATE TABLE users ( id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
-- 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. |
-- 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
NULLis not equal toNULL, so under the standard rulesUNIQUElets rows with a missing value coexist. AddNOT NULLif every row must have a value.- Case and spaces.
'[email protected]'and'[email protected]'are different strings in most databases. Normalize withLOWER()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 = 1in 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
SELECTand then inserting is not safe under concurrency. Insert and handle the unique-violation error, or use upsert. - A foreign key can reference a
UNIQUEcolumn, not just a primary key.
Related
PRIMARY KEY,NOT NULL,CREATE INDEX,UPSERT,FOREIGN KEY- Lessons: Constraints, Keys, Upsert