Unique Index

Hook: Interviewers love this question because it tests whether you know the database, not just the app, must enforce uniqueness under concurrency.

Question: What is a unique index?

Answer: A unique index is an index that also enforces a no-duplicates rule on one column or a combination of columns. It is commonly used for values that must stay one-of-a-kind, like email addresses, usernames, or order numbers. Because the rule lives in the database, it still works when many requests hit the table at the same time.

Interview-Ready Answer: I would say a unique index is a database structure that both speeds up lookups and guarantees that indexed values are not duplicated. I like it for business keys such as email or order_id because it protects data even when two transactions race each other. In most engines it is backed by a B-tree, so the check is usually O(log n).

Detailed Explanation: Think of a unique index as two jobs in one: it is an index, which helps the database find rows quickly, and it is a rule, which says the same key may appear only once. In plain English, it is the database’s built-in way to say, ‘this value must be one of a kind.’

What it is

A normal index is mostly about speed. A unique index adds a promise: for the indexed column or column set, the database will reject duplicates. That promise is enforced inside the storage engine, not in application code, so it still holds when two users click the same button at nearly the same time.

How it works under the hood

1. The client sends an INSERT or an UPDATE that changes an indexed value.
2. The database computes the key values for the index, such as email or (tenant_id, username).
3. It walks the index structure, usually a B-tree. A B-tree is a balanced search tree that keeps data sorted so lookups stay fast.
4. At the leaf level, the engine checks whether the same key already exists and is visible to the current transaction. Visibility is governed by MVCC, or multi-version concurrency control, which is how many databases keep multiple row versions so readers and writers can work together.
5. If the key is free, the row pointer is inserted into the index. If the page is full, the engine may split the page and rebalance the tree.
6. If another transaction is racing to insert the same key, the engine uses locking and visibility rules to decide who wins; one statement succeeds and the other gets a duplicate-key error or waits long enough to see the final outcome.

When and why to use it

• Business keys: emails, usernames, SKU codes, ticket numbers, or tenant-scoped names.
• Composite uniqueness: a pair like (country_code, phone_number) can be unique even if each column repeats on its own.
• Soft deletes: a partial unique index such as WHERE deleted_at IS NULL can enforce uniqueness only for active rows.
• Upserts: features like INSERT ... ON CONFLICT rely on a unique index to know what counts as a conflict.

Unique index vs related features

The important idea is that a UNIQUE constraint is the logical rule, while a unique index is the physical structure that often enforces it. In PostgreSQL, for example, creating a unique constraint automatically creates a unique index behind the scenes. In many databases, the reverse is also true for reads: the same index can both enforce the rule and help queries that filter by that key.

Performance and cost

For exact lookups, a unique index is excellent because the database can jump straight to one key instead of scanning many rows. Time complexity is typically O(log n) for search and insert, and space is O(n) because every row adds an index entry. In practice, B-tree indexes are shallow; even with millions of rows, the tree often has only a few levels, so the work is small. The trade-off is write cost: every insert or update to the indexed columns must also update the index, and page splits can make heavy-write workloads slower. This is why unique indexes are great for read-heavy keys but should not be slapped onto columns that naturally repeat.

Important edge cases

• NULL behavior differs: many engines treat NULL as ‘unknown,’ so multiple NULLs may be allowed, but the exact rule varies by database.
• Case and collation matter: uniqueness follows the database’s comparison rules. If you want Alice@example.com and alice@example.com to count as the same value, you usually need a normalized or expression-based index, such as LOWER(email).
• Existing bad data blocks creation: if duplicates already exist, the unique index build fails until you clean the table.
• Composite keys are about the full pair: (a, b) can repeat in a or b alone, but not in the combination.

So the mental model is simple: a unique index is both a speed tool and a safety net. It lets the database answer ‘is this key already taken?’ quickly and correctly, even when many sessions are writing at once.

Real-World Story: In a subscription signup service, the team stored customers by email. A developer added a pre-check like ‘does this email already exist?’ before inserting, and it looked fine in testing. Under a promotion, two signups with the same email arrived at the same millisecond, both passed the pre-check, and duplicate accounts were created; password resets and invoices started going to the wrong profile.

The fix was to add a unique index on users(email) and handle the duplicate-key error as a normal business rule, often as an HTTP 409 Conflict. If the app forgets to catch that error, the user sees a 500, logs fill with duplicate-key exceptions, and support starts hearing ‘I created an account, but login says my email is already used.’ That is the real value of the unique index: it stops data corruption at the source instead of hoping every code path remembers the rule.

Follow-up & Tricky Questions:

• How is a unique index different from a UNIQUE constraint? A unique constraint is the business rule, while the unique index is the physical structure that usually enforces it. In many databases they end up paired, but the constraint is the semantic promise and the index is the mechanism.
• Can a unique index span multiple columns? Yes, that is called a composite unique index. The combination must be unique, but each individual column can still repeat as long as the pair does not.
• What happens if two transactions insert the same value at the same time? The database prevents both from committing the same key. One transaction succeeds and the other blocks, waits, or fails with a duplicate-key error depending on the engine and timing.
• When would you use a partial unique index? Use it when uniqueness should apply only to a subset of rows, such as active records with deleted_at IS NULL. That is a very common soft-delete pattern.
• Can you make a unique index on an expression? Yes in many engines, and it is a great way to enforce normalized rules such as case-insensitive email uniqueness with LOWER(email).
• Is a primary key just a unique index? Not exactly. A primary key is unique, not null, and there can be only one per table; most databases back it with a unique index, but the rule is stronger than ‘just unique.’
• Can you create one if duplicates already exist? Not successfully. You must clean or deduplicate the data first, or use a partial/filtered strategy that excludes the bad rows.
• Do NULLs count as duplicates? It depends on the database. Some engines allow multiple NULLs in a unique index, while others treat NULL more strictly, so you must know your platform.
• Gotcha: does a unique index make every query faster? No. It helps when the query filters on the indexed key or a leftmost prefix of a composite key; it is not magic for unrelated predicates.

Common Mistakes:

• Mistake: Relying only on application checks. Correction: Always enforce uniqueness in the database so races cannot slip through.
• Mistake: Using a unique index on a column that naturally repeats, like status. Correction: Pick a real business key or use a composite/partial unique index.
• Mistake: Assuming NULL rules are identical in every engine. Correction: Check your database’s exact NULL behavior before you depend on it.
• Mistake: Forgetting to handle duplicate-key errors. Correction: Map them to a clean business response, such as conflict, retry, or ignore with DO NOTHING.

Memory Hook: Picture a nightclub bouncer with a guest list: one name, one entry. The unique index is the bouncer; the database refuses the second person with the same name.

Cheat Sheet:

• Unique index = speed + no duplicates.
• Works on one column or a composite key.
• Great for emails, usernames, order numbers, and tenant-scoped names.
• Usually backed by a B-tree, so lookups are O(log n).
• Writes cost more because every insert/update must update the index.
• NULL, collation, and case rules can vary by database.

Practice Tasks:

• Create a table with email and add a unique index; try inserting a duplicate and observe the error.
• Build a composite unique index on (tenant_id, username) and verify that the same username can exist in different tenants.
• Add a partial unique index for active rows only, then test how soft-deleted records behave.