Schema & Design
UNIQUE Constraints
A UNIQUE constraint declares that the values in one or more columns must be distinct across all rows of a table.
Overview
A UNIQUE constraint declares that the values in one or more columns must be distinct across all rows of a table. It enforces entity integrity for candidate keys that are not the primary key, and it allows NULL values in most SQL implementations, though the treatment of NULLs varies. A table may have multiple UNIQUE constraints, unlike a PRIMARY KEY constraint, which is limited to one per table. UNIQUE constraints can be defined at the column level or at the table level, and they are commonly used to protect natural keys such as email addresses, usernames, or product codes. The constraint is part of the SQL standard since SQL-86 and is supported by every major relational database. When a UNIQUE constraint is created, the database typically builds a unique index to enforce it efficiently, so the constraint and the underlying index are closely related. Because uniqueness is checked on insert and update, violations raise an error and the statement is rejected unless the conflict is handled explicitly by the application or by vendor-specific conflict clauses.
Syntax and usage
A UNIQUE constraint can appear inline with a column definition or as a separate table constraint. Column-level syntax is column_name data_type UNIQUE, optionally with a constraint name such as CONSTRAINT uq_email UNIQUE (email). Table-level syntax is CONSTRAINT constraint_name UNIQUE (column1, column2), which is required for composite uniqueness. Named constraints are easier to drop or alter later. In standard SQL, ALTER TABLE t ADD CONSTRAINT uq_name UNIQUE (col) adds a constraint to an existing table, and ALTER TABLE t DROP CONSTRAINT uq_name removes it. Vendor differences are modest: PostgreSQL, Oracle, SQL Server, and MySQL all accept the standard forms. MySQL permits UNIQUE KEY and UNIQUE INDEX as synonyms, and SQL Server uses DROP CONSTRAINT for both constraints and defaults. Some systems allow UNIQUE on nullable columns and treat multiple NULLs as distinct, while others treat them as equal; this is the most important portability detail to verify. A UNIQUE constraint is not the same as a PRIMARY KEY, because it permits NULLs and does not imply NOT NULL.
How it works
The database enforces a UNIQUE constraint by checking, for every inserted or updated row, whether the proposed key value already exists in the table. To make this check efficient, most engines create a unique index on the constrained columns. The index stores the key values in sorted or hashed form, so the lookup is logarithmic or constant rather than a full table scan. On insert, the engine probes the index; if a matching key is found, the statement fails with a uniqueness violation. On update, the engine checks the new value and, if the key columns are part of the update, verifies that the new combination does not collide with another row. Composite UNIQUE constraints apply the rule to the tuple of columns, so individual columns may repeat as long as the combination is distinct. NULL handling is the main semantic difference between products: in PostgreSQL, Oracle, and SQL Server, multiple rows with NULL in a unique column are allowed because NULL is not equal to NULL, whereas some other systems and certain index types treat NULLs as comparable. This affects how you design optional unique attributes.
Examples
A column-level constraint: CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(255) UNIQUE);. A named table-level constraint on a composite key: CREATE TABLE memberships (user_id INT, group_id INT, CONSTRAINT uq_membership UNIQUE (user_id, group_id));. Adding a constraint to an existing table: ALTER TABLE products ADD CONSTRAINT uq_sku UNIQUE (sku);. Removing it: ALTER TABLE products DROP CONSTRAINT uq_sku;. A failing insert that violates the constraint: INSERT INTO users (id, email) VALUES (2, '[email protected]'); when that email already exists raises a uniqueness error. To handle a conflict in PostgreSQL: INSERT INTO users (id, email) VALUES (2, '[email protected]') ON CONFLICT (email) DO NOTHING;. In MySQL, INSERT IGNORE or ON DUPLICATE KEY UPDATE serves a similar purpose. A composite example that succeeds: INSERT INTO memberships (user_id, group_id) VALUES (1, 10), (1, 20); because the pairs differ even though user_id repeats.
Common pitfalls
The most frequent mistake is assuming a UNIQUE column behaves like a primary key. It does not: it permits NULLs in most engines and does not automatically imply NOT NULL. Another pitfall is expecting multiple NULLs to be rejected; in PostgreSQL, Oracle, and SQL Server they are allowed, so uniqueness of optional values must be enforced another way if required. Composite constraints are often misunderstood: uniqueness applies to the whole tuple, not to each column separately. Dropping a constraint by the wrong name fails, so naming constraints explicitly is safer than relying on generated names. Adding a UNIQUE constraint to a large existing table can be slow because the database must scan and index the data, and it fails if duplicates already exist. In some systems, a unique index and a unique constraint are not perfectly interchangeable, and dropping the constraint may or may not drop the index. Finally, case sensitivity and collation affect uniqueness: values that differ only by case may be considered equal depending on the column collation.
Best practices
Name every UNIQUE constraint explicitly so it can be referenced in migrations and error handling. Prefer table-level constraint syntax when the key spans multiple columns, and keep column-level syntax for simple single-column cases. Decide deliberately whether the column should also be NOT NULL; if the attribute is mandatory, add NOT NULL in addition to UNIQUE. Be aware of collation and case sensitivity before relying on uniqueness for identifiers such as emails or usernames. Create the constraint before loading large volumes of data when possible, or clean duplicates first, because validation scans the whole table. Use the constraint rather than application-only checks, since concurrent transactions can bypass application logic. When a conflict is expected, use a vendor-supported upsert or conflict clause instead of catching and retrying blindly. Document which UNIQUE constraints represent business keys, and review them when schemas evolve, because an obsolete unique rule can block legitimate data.
Frequently asked questions
What is the difference between UNIQUE and PRIMARY KEY? A PRIMARY KEY is a UNIQUE constraint plus NOT NULL, and a table may have only one primary key but many UNIQUE constraints. Does UNIQUE allow NULL values? In standard SQL and in most major databases, yes, and multiple NULLs are usually permitted because NULL is not equal to NULL; check your specific product and collation. Can a UNIQUE constraint be added after data already exists? Yes, with ALTER TABLE ... ADD CONSTRAINT ... UNIQUE (...), but the statement fails if duplicate values are present, so duplicates must be resolved first. Does a UNIQUE constraint create an index? Most implementations create a unique index to enforce it, which also speeds up lookups on those columns, though the exact relationship between the constraint and the index is vendor-specific.