UNIQUE CONSTRAINTDDL
Buy Me A Coffee

Enforce that all values in a column or combination of columns are distinct across all rows. Unlike a PRIMARY KEY, a unique constraint allows NULL values (with engine-specific behavior).

Enforces that all values in a column (or combination of columns) are distinct across the table. Unlike primary keys, multiple NULLs are allowed (in most databases). Useful for enforcing business rules like one active subscription per customer.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all UNIQUE constraints allow multiple NULLs (NULL is not considered equal to NULL). Creating a UNIQUE constraint also creates a UNIQUE index. Composite unique constraints supported. Can be used as a FK target.
PostgreSQL ✓ Supported all NULLs are not considered equal — multiple NULLs are allowed. PG 15+ adds NULLS NOT DISTINCT option to treat NULLs as equal (only one NULL allowed). Unique constraints create an underlying unique index. UNIQUE NULLS NOT DISTINCT is the standard way to enforce at-most-one NULL.
SQL Server ✓ Supported all Only ONE NULL allowed per unique-constrained column (SQL Server treats NULLs as equal for uniqueness purposes — opposite of standard SQL). A filtered unique index (WHERE col IS NOT NULL) can work around this if you need multiple NULLs.
Oracle ✓ Supported all Multiple NULLs allowed (NULL != NULL). Composite unique constraint with all-NULL rows are allowed (partial NULLs enforce uniqueness on non-NULL combinations). Creating a UNIQUE constraint creates a unique index.
SQLite ✓ Supported all Multiple NULLs allowed in UNIQUE columns (NULL != NULL). A UNIQUE constraint on a column creates an implicit index. Constraints cannot be removed after creation without recreating the table.

Details

NULL handling is the key divergence: SQL Server allows only ONE NULL per unique column (treats NULLs as equal), while PostgreSQL, MySQL, Oracle, and SQLite allow multiple NULLs (NULL != NULL). PostgreSQL 15 added NULLS NOT DISTINCT to opt into SQL Server's behavior. This is a subtle but real portability trap.

Standard Syntax

CREATE TABLE users ( id INT PRIMARY KEY, email TEXT UNIQUE, username TEXT, CONSTRAINT uq_username UNIQUE (username) );

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

UNIQUE constraints allow multiple NULLs (NULL is not considered equal to NULL). Creating a UNIQUE constraint also creates a UNIQUE index. Composite unique constraints supported. Can be used as a FK target.

CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) UNIQUE, username VARCHAR(50) ); -- Composite unique: ALTER TABLE users ADD CONSTRAINT uq_name_email UNIQUE (username, email); -- Drop: ALTER TABLE users DROP INDEX uq_name_email;

PostgreSQL

NULLs are not considered equal — multiple NULLs are allowed. PG 15+ adds NULLS NOT DISTINCT option to treat NULLs as equal (only one NULL allowed). Unique constraints create an underlying unique index. UNIQUE NULLS NOT DISTINCT is the standard way to enforce at-most-one NULL.

CREATE TABLE users ( id SERIAL PRIMARY KEY, email TEXT UNIQUE ); -- PG 15+: treat NULL as equal (only one NULL allowed): CREATE UNIQUE INDEX ON users (email) NULLS NOT DISTINCT; -- Composite: ALTER TABLE users ADD CONSTRAINT uq_name UNIQUE (first_name, last_name);

SQL Server

Only ONE NULL allowed per unique-constrained column (SQL Server treats NULLs as equal for uniqueness purposes — opposite of standard SQL). A filtered unique index (WHERE col IS NOT NULL) can work around this if you need multiple NULLs.

CREATE TABLE users ( id INT IDENTITY PRIMARY KEY, email NVARCHAR(255) UNIQUE ); -- Allow multiple NULLs via filtered index: CREATE UNIQUE INDEX uq_email ON users(email) WHERE email IS NOT NULL; -- Drop: ALTER TABLE users DROP CONSTRAINT uq_users_email;

Oracle

Multiple NULLs allowed (NULL != NULL). Composite unique constraint with all-NULL rows are allowed (partial NULLs enforce uniqueness on non-NULL combinations). Creating a UNIQUE constraint creates a unique index.

CREATE TABLE users ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email VARCHAR2(255) UNIQUE ); -- Named constraint: ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email); -- Disable without dropping: ALTER TABLE users DISABLE CONSTRAINT uq_email;

SQLite

Multiple NULLs allowed in UNIQUE columns (NULL != NULL). A UNIQUE constraint on a column creates an implicit index. Constraints cannot be removed after creation without recreating the table.

CREATE TABLE users ( id INTEGER PRIMARY KEY, email TEXT UNIQUE ); -- Composite: CREATE TABLE memberships ( user_id INT, group_id INT, UNIQUE (user_id, group_id) );