Theory
How do you tell two Rahuls apart?
Meera has two customers both named 'Rahul Patel'. By name alone, her database cannot tell them apart, and mixing up their orders would be a disaster.
Databases solve this with keys: columns that guarantee uniqueness and link tables together. But there is a whole family of keys, super, candidate, primary, composite, foreign, unique, and students routinely blur them.
They are not six random terms. They form a clean hierarchy, and once you see the hierarchy, the whole family clicks into place. This is one of the most examined topics in the subject.
Theory
Identifying a citizen
To identify a citizen, Aadhaar + name + address all together works (a super key, but it has extras). Just Aadhaar alone also works and nothing can be dropped (a candidate key, minimal). The government picks Aadhaar as the official ID on forms (the primary key). Sometimes it takes two things together, like state + roll number, to be unique (a composite key). Keys are just IDs, arranged from generous to minimal to chosen.
At a glance
The key family
| Key | Meaning | Note |
|---|---|---|
| Super key | Any columns that identify uniquely | May have extras |
| Candidate key | A MINIMAL super key | Several possible |
| Primary key | The chosen candidate key | Unique + NOT NULL, one per table |
| Composite key | A key of 2+ columns | e.g. order_id + item_id |
| Foreign key | Points to another table's primary key | Links tables |
| Unique key | Enforces uniqueness | CAN be null; several allowed |
Theory
The hierarchy: super to candidate to primary
These three are one ladder:
- A super key is any column-set that identifies a row uniquely, even with extra baggage (customer_id + name is a super key; the name is unnecessary).
- Strip it to the minimum that still identifies uniquely and you have a candidate key (customer_id alone). A table can have several candidate keys (customer_id and phone number, if both are unique).
- Pick one candidate key as the official identifier and it becomes the primary key: unique, never null, one per table.
So: every candidate key is a super key; the primary key is the chosen candidate.
Theory
Foreign keys: the links between tables
The foreign key is the star of relational design. It is a column in one table that refers to the primary key of another table, and it is how tables connect.
Meera's Order table has a customer_id column that is a foreign key pointing to the Customer table's primary key. That single link says 'this order belongs to that customer', and lets the database keep them consistent (you cannot order for a customer who does not exist, referential integrity).
Remember the many-to-many junction table? It works entirely through foreign keys pointing at both parents.
Quiz
What is the difference between a candidate key and the primary key?
- Candidate keys are all the minimal unique identifiers; the primary key is the ONE chosen from them
- They are the same thing with different names
- A candidate key can be null but a primary key is always text
- The primary key has extra columns the candidate key lacks
Show the answer
Candidate keys are all the minimal unique identifiers; the primary key is the ONE chosen from them
A table may have several candidate keys (each a minimal unique identifier, e.g. customer_id and a unique phone). The primary key is the single candidate key you choose as the official one, and it must be unique and NOT NULL. So 'candidate' is the pool of eligible minimal keys; 'primary' is the elected one. This candidate-vs-primary distinction is a guaranteed exam question.
Think first
Why an order-item needs a composite key
In Meera's order-items table, order_id repeats (many items per order) and item_id repeats (an item appears in many orders). Neither alone is unique. What kind of key uniquely identifies a row, and why?
Show the answer
A composite key: order_id + item_id together. Neither column is unique on its own (both repeat), but the combination is unique, order 5001 contains item 'Sugar' exactly once. A key made of two or more columns is a composite key, and junction tables almost always use one. (Here both columns are also foreign keys pointing to the Order and Item tables, doing double duty.)
Watch out
Where marks leak
Blurring super (has extras) vs candidate (minimal) vs primary (chosen, unique + NOT NULL, one). Forgetting a primary key cannot be null while a unique key can. Saying a foreign key points to 'another table', be precise: it points to another table's primary key. And missing that a composite key is multiple columns together. This topic is a marks goldmine precisely because the terms are easy to confuse, learn the ladder.
Theory
You will type these in Unit 4
Every key here becomes a constraint you will write in SQL next unit: PRIMARY KEY, FOREIGN KEY, UNIQUE. The theory now is the syntax later. And the Gri-Learn database you are reading from uses foreign keys to link lessons to topics to subjects, exactly this design. Next lesson is the big payoff: why we normalize, the anomalies that a badly-keyed table suffers, Meera's breaking spreadsheet finally explained.
Summary
Key takeaways
- Super key: any column-set that identifies a row uniquely (may have extras).
- Candidate key: a minimal super key; a table can have several.
- Primary key: the ONE chosen candidate key, unique and NOT NULL, one per table.
- Composite key: a key made of two or more columns together (order_id + item_id).
- Foreign key: a column referring to another table's PRIMARY KEY; links tables (referential integrity).
- Unique key: enforces uniqueness but CAN be null and several are allowed.
- Memory hook: super (with baggage) to candidate (minimal) to primary (elected); foreign keys link tables.