CSE Roadmap

Core tier · about 2 hours · 5 parts

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.