Unique Index

Two jobs in one structure

A unique index does double duty: it enforces uniqueness on the indexed column(s) — no two rows may share the same value — while also functioning as a regular index, speeding up lookups on that column exactly like any other index would.

sql
CREATE UNIQUE INDEX idx_email_unique ON students (email);

This guarantees no two students can share the same email address (attempting to INSERT or UPDATE a duplicate raises a constraint violation), while simultaneously making WHERE email = '...' lookups fast, since the underlying structure is typically a B+ Tree just like a regular index.

The connection to constraints you already know

You may recall the UNIQUE constraint from an earlier chapter on constraints (Chapter 10). In most database engines, declaring a column UNIQUE — or declaring it a PRIMARY KEY — automatically creates a unique index behind the scenes to enforce that guarantee efficiently. The engine needs some fast way to check "does this value already exist?" on every insert/update, and a unique index is exactly the mechanism used to make that check efficient rather than requiring a full table scan on every write.

So PRIMARY KEY and UNIQUE aren't really separate from indexing — they are, in effect, a request for the engine to build and maintain a unique index automatically.

Why enforcing uniqueness via an index (not just application logic) matters

If uniqueness were only checked in application code (e.g., "check no student has this email before inserting"), a race condition between two simultaneous inserts could still let two duplicate rows slip through. A unique index enforces the constraint at the database level, atomically, closing that race condition — the database itself refuses the second conflicting write, no matter what the application code did or didn't check.

Composite unique indexes

A unique index can also span multiple columns, e.g., UNIQUE (department, id) would allow the same id to repeat across different departments but not within the same department — the uniqueness guarantee applies to the combination of columns, not each column individually.

Edge cases

• Most engines allow multiple NULL values in a unique-indexed column even though every non-NULL value must be distinct — because NULL is treated as "unknown," and two unknowns are not considered "equal" to each other (behavior can vary slightly by engine).
• A unique index still incurs the same write-overhead trade-off as any other index (20.14) — every insert/update must check the structure for a duplicate before proceeding.
• Declaring UNIQUE on a column that already has significant duplicate data will fail to create the index (or the constraint) until the duplicates are resolved.

Interview-style Q&A

Q: What are the two things a unique index does simultaneously? A: It enforces that no two rows share the same value (or combination of values) in the indexed column(s), and it also functions as a normal index for fast lookups.

Q: What's the relationship between a PRIMARY KEY/UNIQUE constraint and a unique index? A: Declaring a column PRIMARY KEY or UNIQUE typically causes the engine to automatically create a unique index behind the scenes to enforce and efficiently check that constraint.

Key takeaway: a unique index is not a separate concept from the UNIQUE/PRIMARY KEY constraints you already know — it's the actual mechanism the engine uses to enforce and efficiently check them.