UNIQUE Constraint
SQL · Working with Tables
Theory
A UNIQUE constraint guarantees that no two rows share the same value in a column, or the same combination of values across several columns. email VARCHAR(150) UNIQUE stops two employees from registering the same email; UNIQUE (dept, emp_code) allows the same code in different departments but not twice in one.
The engine enforces it with a unique index, which also speeds up lookups on that column. Unlike a primary key, a table can have many UNIQUE constraints, and the columns may be nullable. Because NULL is unknown, most engines (PostgreSQL, MySQL, Oracle, SQLite) allow many NULLs in a UNIQUE column, while SQL Server allows only one. Uniqueness is checked exactly, so 'Asha@mail.in' and 'asha@mail.in' are different unless the collation ignores case.
Why we use it
Some business values must never repeat even though they are not the primary key: emails, phone numbers, PAN or GST numbers, product codes. UNIQUE enforces that rule in the database instead of hoping the application checks it.
When to use it
Use UNIQUE for alternate identifiers and natural keys when the primary key is a surrogate id. If the value must also always be present, combine it with NOT NULL. Use the primary key for the main row identifier.
How it works
- INSERT or UPDATE a row
- Is the value NULL? Yes: Usually allowed (SQL Server allows one NULL). No: Check the unique index.
- Does another row already have this value? Yes: Reject: duplicate value. No: Accept the row.
- Add the value to the unique index
- Values stay distinct
Syntax
column datatype UNIQUEExample
CREATE TABLE employees (
email VARCHAR(150) UNIQUE
);Key points
- Unlike PRIMARY KEY, a table can have several UNIQUE constraints
- NULL values are generally allowed and not considered duplicates
- SQL Server allows only one NULL in a UNIQUE column
- Can cover a combination of columns: UNIQUE (dept, emp_code)
- Backed by a unique index, which also speeds up lookups