UNIQUE CONSTRAINT SQL Compatibility | MySQL, PostgreSQL, SQL Server, Oracle, SQLite

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

SQL UNIQUE CONSTRAINT Compatibility Across Databases
Database System	Support Status	Since Version	Notes
MySQL	Native	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	Native	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	Native	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	Native	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	Native	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: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Native in all listed versions

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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) );