Part 2 of 5 · 1 min read
Super key and candidate key
What makes a set of columns able to identify a row uniquely.
Think of a locker room where every locker has a combination lock. A single number on the lock might not be enough to tell two lockers apart, but a number plus the row of lockers it sits in always is.
In plain words: a super key is any set of columns whose values, taken together, are never repeated across two different rows. A table can have many super keys, some larger than they need to be. A candidate key is a super key with no wasted columns: remove any one column from it, and it stops being unique. Every table has at least one candidate key, even if that key is every column together.
A worked example. In customers (customer_id, full_name, address), the set {customer_id, full_name} is a super key: no two rows share both those values. But it is not a candidate key, because {customer_id} alone is already enough. {customer_id} alone is a candidate key: it is unique, and removing it leaves nothing.
The key lines. Checking uniqueness directly in SQL, the way this would actually be tested against real data:
SELECT customer_id, COUNT(*) FROM customers GROUP BY customer_id HAVING COUNT(*) > 1;
An empty result confirms customer_id alone is unique across every row.
Super key, candidate key
A super key is any set of columns whose combined values never repeat across two rows of a table. A candidate key is a super key with no extra columns: every column in it is needed to keep the uniqueness.
*Example:* `{customer_id, full_name}` is a super key for `customers` but not a candidate key, since `{customer_id}` alone already works.