Unique Key

Hook: A unique key is the bouncer at the door: every row gets in once, but never twice.

Question: What is a unique key in SQL?

Answer: A unique key is a column, or a combination of columns, that cannot contain duplicate values. Databases usually enforce it with a unique constraint and, under the hood, often a unique index. It is different from a primary key because it does not have to be the table’s main identifier, and in many databases it can still allow NULL values.

Interview-Ready Answer: I’d say a unique key is a rule that guarantees no two rows share the same value, or the same combination of values, in that column set. The database usually backs it with a unique index, so duplicate inserts and updates are rejected efficiently. One important detail is that UNIQUE usually does not imply NOT NULL, unlike PRIMARY KEY.

What a unique key really means

Detailed Explanation: Think of a unique key as a promise to the database: ‘this value identifies at most one row in this table.’ It can be a single column like email, or a multi-column combination like (tenant_id, external_id). Interviewers like this topic because it reveals whether you understand both data modeling and enforcement, not just syntax.

How it works under the hood

1. You define a unique constraint in the table definition, or sometimes add it later with ALTER TABLE.
2. The database creates metadata for the rule and, in most engines, a unique index to enforce it quickly.
3. When you insert or update a row, the engine checks whether the candidate key already exists.
4. If a matching value is found, the write is rejected with a uniqueness error.
5. If no match exists, the row is written and the index is updated at the same time.
6. Because the check uses an index tree rather than scanning every row, the lookup is usually O(log n) instead of O(n).

This means unique keys are fast for reads and enforcement, but they do add overhead on writes because every insert and key-changing update must also maintain the index.

When and why to use it

• Use it for real business identifiers such as email, username, SKU, or tenant-scoped external IDs.
• Use a composite unique key when one column alone is not enough, such as ‘room number must be unique within a hotel’.
• Use it when duplicate data would cause real bugs: double signups, duplicate payments, duplicate inventory items, or conflicting user profiles.

Primary key vs unique key vs unique index

A primary key is the official identity of the row. A unique key is another candidate identity, such as email or employee badge number. A unique index is the structure the engine often uses to make the rule fast; the constraint is the logical rule, while the index is the physical mechanism.

Important edge cases

• NULL behavior: Most databases treat NULL as unknown, so multiple NULLs are allowed in a UNIQUE column. SQL Server is a common exception people forget, because it has historically allowed only one NULL in a unique constraint/index.
• Case sensitivity: Alice@example.com and alice@example.com may or may not collide depending on collation. For emails, teams often normalize to lowercase or use a case-insensitive type/index.
• Composite uniqueness: (tenant_id, slug) can be unique even if slug alone repeats across tenants. That is a common multi-tenant pattern.
• Soft deletes: If old rows stay in the table with deleted_at, you may need a partial unique index in databases that support it, so only active rows must stay unique.

Performance note: Each unique key adds storage and write cost, but the benefit is strong correctness and fast duplicate detection. That trade-off is usually worth it for business-critical identifiers.

Real-World Example: Imagine a checkout or account-signup service in an e-commerce app. The team stores one row per customer and puts a unique key on email so the same person cannot create two accounts with the same address. They also use a composite unique key like (tenant_id, external_customer_ref) when importing users from partner systems, because the external ID only has meaning inside one tenant.

What goes wrong when someone misunderstands this? A developer forgets the unique constraint during a migration, and duplicate accounts start slipping in. Users then see two profiles, duplicate welcome emails, and confusing login failures because one email maps to multiple rows. In the logs you may see messages like ‘duplicate key value violates unique constraint’, support tickets spike, and order history starts attaching to the wrong account. The bug is not just cosmetic; it can become a data integrity incident.

The lesson interviewers want: unique keys are not just about ‘keeping things tidy.’ They protect business rules at the database level, where the guarantee is strongest.

Follow-up & Tricky Questions:

• Can a unique key contain NULLs? Usually yes. In many databases, NULL means unknown, so more than one NULL is allowed. The big exception to remember is SQL Server, where the behavior is stricter than many candidates expect.
• Can a table have more than one unique key? Yes. A table can have several unique constraints, such as one for email, one for username, and one composite key for tenant-scoped external IDs.
• What is the difference between UNIQUE and PRIMARY KEY? PRIMARY KEY is the official row identifier and cannot be NULL. UNIQUE is an alternate identifier and is more flexible, so you can have many of them in one table.
• Does UNIQUE automatically create an index? In most real databases, yes. The engine usually creates a unique index or equivalent structure so duplicate checks are fast.
• How do you enforce case-insensitive uniqueness for email? You either normalize the data to one case before saving, or you use a case-insensitive type, collation, or functional index depending on the database.
• Does UNIQUE on (a, b) make a unique by itself? No. It only says the pair must be unique together. The same a value can appear many times if b is different.
• Is a unique key the same as a unique index? Not exactly. The unique key is the rule; the unique index is the physical structure that often enforces it.
• Can two NULLs violate uniqueness? Usually no in PostgreSQL and many other engines, because NULL is treated as not equal to NULL. That is a classic gotcha.

Common Mistakes:

• Confusing UNIQUE with PRIMARY KEY. Correction: a unique key is an alternate identifier; a primary key is the main identifier and cannot be NULL.
• Thinking UNIQUE means no NULLs. Correction: many databases allow NULLs in unique columns, so check the engine’s rule before relying on it.
• Assuming one-column uniqueness from a composite key. Correction: UNIQUE(a, b) only blocks duplicate pairs, not duplicate a values.
• Forgetting case and collation rules. Correction: email uniqueness can fail if the database treats letter case as different and your app does not normalize it.

Memory Hook: Primary key = passport. Unique key = one-of-a-kind room key. One row has the passport, but many doors in the database can still require a key that only fits one lock.

Cheat Sheet:

• Unique key = no duplicate values in a column or column set.
• Usually enforced by a unique constraint backed by a unique index.
• Primary key is stricter: one per table, no NULLs.
• Composite unique keys protect multi-column business rules.
• NULL handling and case sensitivity depend on the database.
• Write cost goes up a little; data safety goes up a lot.

Practice Tasks:

• Add a unique constraint to a users table on username.
• Create a composite unique key for (tenant_id, slug) and test a duplicate insert.
• Try the same script in two databases and compare how they treat NULLs in unique columns.