Super Key

Hook: Interviewers like this question because it checks whether you understand how a table can identify one row without accidentally picking the wrong row later.

Question: What is a super key in SQL?

Answer: A super key is any set of one or more columns that can uniquely identify a row in a table. If a set works, then adding extra columns still keeps it a super key, even if those extra columns are not needed. A candidate key is the smallest possible super key, and the primary key is the one the database chooses as the main identifier.

Interview-Ready Answer: A super key is any column or combination of columns that uniquely identifies each row in a table. For example, if user_id is unique, then user_id is a super key, and user_id, email is also a super key because adding columns does not remove uniqueness. The important follow-up is that a candidate key is the minimal super key, and the primary key is the chosen candidate key that the database enforces most strongly.

What it means

Detailed Explanation: A super key is about uniqueness, not size. If a set of columns guarantees that no two rows share the same combined values, then that set is a super key. The set may be tiny, like one column, or larger, like a multi-column combination.

How it works under the hood

1. Pick one row in the table and look at the columns that might identify it.
2. Check whether the chosen column set produces exactly one match for every row.
3. If yes, that set is a super key because it can point to a single row without ambiguity.
4. If you can remove any column and it still stays unique, then the set was not minimal.
5. If it is minimal, it becomes a candidate key, which is the smallest useful identifier.
6. The database usually turns a primary key or unique constraint into an index, so uniqueness checks are fast.

Super key vs candidate key vs primary key

When and why to use the idea

You use super key thinking when you design tables and ask, What really identifies this row? In a school table, student_id may be enough. In a multi-tenant system, the pair tenant_id and order_no may be needed because the same order number can repeat in different tenants. This is why composite keys matter: sometimes one column is not enough.

Important SQL reality

In theory, keys are about relational design. In SQL, PRIMARY KEY means unique and not null, while UNIQUE means values must not repeat. However, many databases allow multiple NULL values in a UNIQUE column, so a nullable unique column is not always a practical row identifier. That is a common interview trap.

Performance notes

With a primary key or unique constraint, the database usually builds a B-tree index, which makes insert and lookup checks about O(log n) instead of O(n) table scans. On a million-row table, that usually means a small number of page reads rather than scanning every row. Without an index, proving uniqueness can become expensive very quickly.

Memory model

Memory Hook: Think of a super key as a key ring: one working key is enough to open the door, and adding extra keys to the ring does not stop it from opening. The smallest key ring that still works is the candidate key.

Real-World Story: Imagine a multi-tenant checkout service for an online store. Each merchant has its own order_no, so order_no alone is not enough to identify an order across all merchants. The safe identifier is the combination tenant_id + order_no, which is a composite super key.

One day, a developer writes a join using only order_no. For merchant A and merchant B, both have order 1001, so the query matches two rows instead of one. The symptom is duplicate invoices, incorrect shipment status, and support tickets saying a customer saw another merchant’s order details. In logs, you may see ambiguous lookups, unexpected row counts, or application code failing with a message like more than one row returned.

The lesson is simple: a super key is not just academic language. It is the difference between a row being uniquely identifiable and a system quietly mixing up two customers.

Follow-up & Tricky Questions:

• How is a super key different from a candidate key? A super key can have extra columns, while a candidate key is minimal. If removing any column still leaves the set unique, it was not a candidate key yet.
• Is every primary key a super key? Yes. A primary key is chosen because it uniquely identifies each row, so it is automatically a super key.
• Can a table have more than one candidate key? Yes. A table can have several minimal unique column sets, and the database design chooses one of them as the primary key.
• Why would you use a composite super key? When no single column is unique across all rows, but a combination is, such as tenant_id plus order_no.
• Does UNIQUE always mean key? Not always in practice if the column allows NULL. Many SQL databases allow multiple NULLs in a unique column, so it may not identify every row cleanly.
• Tricky: If id is unique, is id, name also a super key? Yes, because uniqueness is preserved when you add more columns. It is just a non-minimal super key.
• Tricky: Can a super key contain repeated values in one column? Yes, as long as the full combination is unique. A composite key can repeat one part and still be unique overall.
• Tricky: Is a nullable unique column a safe primary key? No. A primary key must be not null, and SQL uniqueness rules around NULL can make the column unsuitable as a real identifier.

Common Mistakes:

• Confusing super key with candidate key: Fix it by remembering that a candidate key is the smallest super key, not just any unique set.
• Forgetting that extra columns do not break uniqueness: If id is unique, then id, anything is still unique.
• Assuming UNIQUE always means fully identifying: Check whether the column allows NULL, because that can change the practical meaning.
• Using a natural column that can change: Email or username may be unique today, but if it changes often, it is a weaker design choice than a stable ID.

Memory Hook: One working key on the ring is enough; the smallest working ring is the candidate key.

Cheat Sheet:

• Super key = any set of columns that uniquely identifies a row.
• Candidate key = minimal super key.
• Primary key = chosen candidate key.
• Adding columns to a super key keeps it unique.
• PRIMARY KEY implies unique and not null.
• UNIQUE with NULL can be a trap.

Practice Tasks:

• Create a table with a single-column primary key and name two different super keys for it.
• Design a composite key for a multi-tenant orders table.
• Try inserting duplicate and NULL values into a UNIQUE column and observe what your database allows.