A unique index is a database index that guarantees that no two rows share the same value in the indexed column or combination of columns, while also providing the fast lookups of an ordinary index. In indexing, it serves two roles at once: it is an integrity mechanism that rejects duplicate inserts and updates, and an access path the optimizer can use to find a single row with minimal work.
In Databases, unique indexes underpin primary keys and unique constraints in almost every relational system, including PostgreSQL, MySQL, SQL Server, Oracle and SQLite. They are the only race-free way to enforce uniqueness under concurrent writes, which is why application-level checks alone are never sufficient.
How a unique index enforces uniqueness
A unique index is usually implemented as a B-tree index with one extra rule: before a new entry is added, the engine searches the tree for an existing equal key. If one exists and belongs to a live row, the statement fails with a duplicate key error, such as SQLSTATE 23505 in PostgreSQL or error 1062 in MySQL. Because the check and the insertion happen inside the index structure under short-term locks, two concurrent transactions cannot both succeed with the same value.
When transactions overlap, the second writer typically waits for the first to commit or roll back before the check is decided. If the first commits, the second fails; if it rolls back, the second proceeds. This behavior is what makes unique indexes reliable where a “check then insert” sequence in application code would allow duplicates.
The table shows how each lookup type benefits from the uniqueness guarantee.
| Access pattern | Non-unique index | Unique index |
|---|---|---|
| Equality on full key | Range of matching entries | At most one entry, search stops early |
| Row estimate for optimizer | Based on statistics | Exactly zero or one row |
| Join on indexed key | May multiply rows | Each outer row matches at most once |
| Range query | Ordered scan | Ordered scan, same as non-unique |
| Insert cost | Add entry | Search for duplicate, then add entry |
Unique index, unique constraint and primary key
A unique constraint is the logical rule declared in the schema, and in most systems it is implemented by a unique index created automatically. A primary key is a unique constraint that additionally forbids NULL and identifies the row; a table can have only one primary key but many unique constraints or indexes.
| Mechanism | Allows NULL | Per table | Can be a foreign key target |
|---|---|---|---|
| Primary key | No | One | Yes |
| Unique constraint | Yes, with system-specific rules | Many | Yes |
| Unique index | Yes, with system-specific rules | Many | Depends on the system |
| Partial unique index | Yes | Many | Usually no |
Declaring a constraint rather than a bare index is generally preferred, because it documents intent in the schema and is recognized by tools, ORMs and foreign key definitions.
NULL handling and other system differences
The most important portability trap is how NULL values are treated. Under the SQL standard, NULL is not equal to NULL, so most systems allow many NULL rows in a unique index. SQL Server traditionally allows only one NULL per unique index, and PostgreSQL 15 added the option NULLS NOT DISTINCT to make NULLs count as duplicates.
| System | Multiple NULLs allowed | Partial or filtered unique index | Notes |
|---|---|---|---|
| PostgreSQL | Yes by default | Yes, WHERE clause | NULLS NOT DISTINCT from version 15 |
| MySQL InnoDB | Yes | No; functional indexes instead | Prefix indexes limit uniqueness to the prefix |
| SQL Server | No, one NULL only | Yes, filtered index | Use WHERE col IS NOT NULL to allow many |
| Oracle Database | Yes, fully NULL keys not indexed | Via function-based index | Composite keys with some NULLs are checked |
| SQLite | Yes | Yes, partial index | Same rules as standard behavior |
Collation also matters: in a case-insensitive collation, “Ana@x.com” and “ana@x.com” collide, while in a binary collation they are distinct values.
Designing unique indexes in practice
A unique index should mirror a real business rule, such as one account per email address, one invoice number per company, or one active subscription per customer. Composite unique indexes enforce uniqueness of a combination, for example (tenant_id, invoice_number) in a multi-tenant system, where the same number may appear in different tenants.
- Use partial unique indexes for conditional rules, such as uniqueness only among rows where
deleted_at IS NULL. - Normalize values first, for instance by indexing
lower(email), when the rule is case-insensitive. - Build on large live tables carefully: PostgreSQL offers
CREATE UNIQUE INDEX CONCURRENTLY, and SQL Server Enterprise supports online index builds. - Clean existing duplicates first, because creating a unique index fails if any duplicates are present.
- Use upserts such as
ON CONFLICTto turn expected collisions into updates instead of errors.
Common mistakes and their symptoms
Most problems arise when teams rely on application checks instead of the database, or misunderstand which values the index actually compares.
| Symptom | Cause | Fix |
|---|---|---|
| Duplicate accounts under load | Uniqueness checked only in code | Add a unique constraint |
| Duplicates differing only in case | Case-sensitive comparison | Unique index on a normalized expression |
| Soft-deleted rows block reuse | Index covers deleted rows | Partial unique index on active rows |
| Index creation fails | Existing duplicates in data | Find and resolve duplicates first |
| Unexpected NULL conflicts after migration | Different NULL semantics | Review NULL rules per system |
Put simply
A unique index is like a guest list that refuses to write the same name twice. When a new entry arrives, the database first checks the list. If the name is already there, the new entry is turned away.
Because the database does this check itself, it works even when many people sign up at the same moment. The same list also helps the database find any single entry very quickly.
In practice
A subscription platform discovered that some customers had two accounts with the same email address. The sign-up code checked for an existing email before inserting, but when users double-clicked the button, two requests passed the check at the same time and both inserted a row.
The team merged the existing duplicates, then created a unique index on lower(email) limited to active accounts. The sign-up code now catches the duplicate key error and shows a friendly message. Since the change, no duplicate accounts have been created, and login lookups by email use the same index to find the account in a single index probe.