Candidate Key

Hook: Interviewers love this because it tests the tiny but important difference between unique and minimal unique.

Question: What is a candidate key in SQL?

Answer: A candidate key is the smallest set of column(s) that can uniquely identify one row in a table. Smallest means you cannot remove any column without losing uniqueness. SQL does not have a special CANDIDATE KEY clause; you usually express it with PRIMARY KEY or UNIQUE plus NOT NULL.

Interview-Ready Answer: A candidate key is any minimal set of columns that uniquely identifies a row. In plain SQL, I do not declare a candidate key directly; I enforce it with a PRIMARY KEY or a UNIQUE constraint, and I make sure the columns are NOT NULL. If a table has more than one candidate key, I choose one as the primary key and keep the others as alternate keys.

What a candidate key is

In the relational model, a candidate key is the smallest set of columns that still uniquely identifies each row. Unique means no two rows can share the same values in that set. Smallest means if you remove any one column, the uniqueness guarantee breaks.

Think of it like an ID card: if the columns together are enough to point to one person, but no smaller subset works, you have a candidate key. A table can have more than one candidate key, which is why the word candidate matters: several options are eligible, and the database designer chooses one as the primary key.

How to find one under the hood

1. Start with the business rule: what fields are supposed to identify one real-world thing, such as a customer, an order line, or a passport record.
2. Check uniqueness. If the same column values can appear in two rows, that set is not a key.
3. Remove extra columns one by one. If the set is still unique after removing a column, the bigger set was only a superkey (a unique set that may include extra columns), not a candidate key.
4. Repeat until every remaining set is minimal. Each minimal set is a candidate key.
5. Pick one stable candidate key as the primary key. Keep the rest as alternate keys using UNIQUE constraints.

Candidate key vs other key types

How SQL expresses it

SQL does not have a separate CANDIDATE KEY keyword. You model candidate keys with constraints: PRIMARY KEY for the chosen one, and UNIQUE plus NOT NULL for the others. That distinction matters because UNIQUE alone is not always enough: NULL handling differs by database, and a candidate key in the relational sense cannot contain NULL at all.

Performance and practical trade-offs

Enforcing a candidate key usually means creating a unique index. That adds space overhead, often O(n) for the index, and makes inserts and updates a little slower because the database must check uniqueness, usually with an index lookup of about O(log n). On a large table, a B-tree index might be only 3 to 4 levels deep for millions of rows, so the check is still fast, but it is not free.

There is also a design trade-off between natural and surrogate keys. A natural key is a real-world value such as email or national_id; a surrogate key is a made-up identifier such as an integer id. Natural candidate keys are easy for humans to understand, but they can change and may be wider. Surrogate keys are stable and narrow, but you still often keep the natural candidate keys as UNIQUE constraints so the business rule is enforced.

Important edge cases

• NULLs: a primary key cannot be NULL. For UNIQUE constraints, behavior with NULL differs by engine, so do not assume uniqueness alone is enough.
• Composite keys: a candidate key can be more than one column, like (order_id, line_no). That is common in join tables and line-item tables.
• Mutable data: email can be a candidate key, but if users change emails often, updates and foreign keys can become painful.
• Non-minimal uniqueness: (customer_id, email) may be unique, but if customer_id alone is enough, then the pair is only a superkey, not a candidate key.

Real-world story

Imagine an e-commerce checkout service. Each customer has a system id, an email address, and a national_id from a verified sign-up step. The team correctly uses customer_id as the primary key, but they also keep email and national_id as candidate keys because each one should identify exactly one customer.

One day, a developer builds a merge job that matches accounts using full_name and phone number instead of a real candidate key. It works in testing, then fails in production when two different people share the same name and change phone numbers. The symptoms are messy: duplicate customer records, orders linked to the wrong account, refund emails sent to the wrong person, and logs full of foreign key errors when the cleanup job tries to rewire rows.

The business impact is serious: support tickets spike, loyalty points split across accounts, and finance cannot trust the join between payments and customers. The fix is to identify the true candidate keys from the business rules, enforce them with UNIQUE constraints, and never use a non-key field as the identity of a person just because it looks convenient.

Follow-up & Tricky Questions:

• How is a candidate key different from a primary key? A candidate key is any minimal unique identifier, while the primary key is the one candidate key the database chooses as the main row identifier. A table can have many candidate keys but only one primary key.
• What is a superkey? A superkey is any set of columns that uniquely identifies a row, even if it has extra columns. Every candidate key is a superkey, but not every superkey is a candidate key because candidate keys must be minimal.
• Can a table have multiple candidate keys? Yes. For example, a customer table might have customer_id, email, and national_id as separate candidate keys if each one is unique and minimal.
• What is a composite candidate key? It is a candidate key made from more than one column, such as (order_id, line_no). This is common when one column by itself is not enough to identify a row.
• How do you enforce candidate keys in SQL? Use PRIMARY KEY for the chosen key and UNIQUE with NOT NULL for the other candidate keys. That is how you turn the design idea into a database rule.
• Is a surrogate key a candidate key? It can be, if it is unique and minimal, but in practice people use that term for a system-generated key like an integer id. The important interview point is that surrogate keys are usually chosen for stability, while natural candidate keys come from the business.
• Tricky: Is UNIQUE enough to make something a candidate key? Not always. A candidate key must be minimal and non-null in the relational sense, and different databases treat NULL in UNIQUE constraints differently, so you usually want UNIQUE plus NOT NULL.
• Tricky: Can a candidate key change over time? The business rule can change, so yes, the set of candidate keys can change with schema evolution. That is why changing a business key like email requires careful migration, backfills, and foreign key updates.
• Tricky: Is every primary key a candidate key? Yes. A primary key is simply the candidate key the designer picked as the main identifier.

Common Mistakes:

• Confusing candidate key with primary key. Fix: a primary key is one chosen candidate key, not a different concept.
• Forgetting minimality. Fix: if a larger set is still unique after removing a column, it is only a superkey, not a candidate key.
• Assuming UNIQUE automatically means candidate key. Fix: candidate keys must also be minimal and, in practice, should be NOT NULL.
• Using a changing column without thinking. Fix: email or phone may be a valid candidate key, but stable surrogate keys are often safer for foreign keys and joins.

Memory Hook: Think of a candidate key as the smallest keyring that still opens exactly one door. If you can remove a key and still open the same door, it was not a candidate key yet.

Cheat Sheet:

• Candidate key = minimal unique identifier.
• Superkey = unique, but may have extra columns.
• Primary key = chosen candidate key.
• Alternate key = candidate key not chosen as primary.
• In SQL, use PRIMARY KEY or UNIQUE + NOT NULL.
• Composite candidate keys are normal, especially in line-item tables.

Practice Tasks:

• Take a customers table and list every possible candidate key from the business rules.
• Add SQL constraints that enforce one natural candidate key and one composite candidate key.
• Design an order_items table where (order_id, line_no) is the primary key, then explain why line_no alone is not enough.