What is a key?
A key is an attribute (column) or a set of attributes that helps you uniquely identify a row (tuple) in a relation (table).
Why we need keys:
- To tell any two rows apart, even when other values repeat.
- To enforce integrity (entity integrity needs a primary key).
- To build relationships between tables (foreign keys).
We'll learn eight kinds, in this order: super → candidate → primary → alternate → foreign → composite → compound → surrogate. All the examples use this employee table:
Super key
A super key is any set of columns that uniquely identifies every row. A single column can do it, and so can any combination that contains such a column.
{emp_id},{aadhar},{email}: each is unique on its own.{emp_id, email},{aadhar, email},{emp_id, aadhar},{emp_id, aadhar, email},{emp_id, name}…: still unique (anything containing a unique column is).{name},{dob},{name, dob}: not super keys here, because two employees share them.
Sometimes no single column is unique and you need a combination. The whole collection of all such sets is the set of super keys.
Candidate key
A candidate key is a minimal super key: the minimum number of columns needed to uniquely identify rows. Remove any column and it stops being unique.
{emp_id}✓,{aadhar}✓,{email}✓{aadhar, email}✗: not minimal, sinceaadharalone already works.
So the candidate keys are emp_id, aadhar, email. Every candidate key is a super key, but not every super key is a candidate key.
Primary key
The primary key is the candidate key you finally choose to identify rows. Rules for choosing it:
Always unique
No two rows may ever share it.
Never NULL
Every row must have a value.
Rarely changes
A stable value; changing it ripples everywhere it's referenced.
Simple / small
Ideally a single, short column (often a number).
Every Indian has a unique Aadhaar number, so it looks perfect. But at a company like Infosys, foreign employees don't have an Aadhaar, so their value would be NULL, and a primary key can never be NULL. ✗
Email is unique and usually present, but people change their email, and a primary key should rarely change. It's also a long string. Not ideal.
emp_id is unique, never null, never changes and is a small integer. It becomes the primary key.
Alternate key
The candidate keys that were not chosen as primary key are alternate keys:
-- alternate keys = candidate keys − primary key
-- {emp_id, aadhar, email} − {emp_id} = {aadhar, email}Foreign key
A foreign key is a column in one table that refers to the primary key of another table. It creates the relationship between the two tables and enforces referential integrity.
With the foreign key in place you can add rules such as "if a branch is renamed, update all its students" or "you can't delete a branch while students still belong to it". A table can have many foreign keys, and the foreign key column itself can repeat (many students share a branch).
Composite key
A composite key is a key made of more than one column, e.g. {emp_id, email} or {aadhar, email}. You need one when no single column is unique on its own.
Compound key
A compound key is a composite key in which at least one column is a foreign key.
-- enrollments: which student took which course
-- (student_id, course_id) together identify a row
-- and BOTH are foreign keys → a compound key
enrollments(student_id → students, course_id → courses, enrolled_on)Surrogate key
Sometimes your data has no natural column (or small combination) that's unique. Then you create an artificial one yourself, usually an auto-incrementing id. That self-made key is a surrogate key. It has no real-world meaning; it exists only to identify rows. The user_id in Chapter 2 is exactly this.
All eight at a glance
Cardinality of relationships
When two tables (entities) are related, the cardinality of the relationship is the number of instances of one entity that can be associated with instances of the other. There are three kinds (or there's no relationship at all, e.g. students and the college's admin staff):
One row on each side matches at most one row on the other. Example: in a lab, each student has one project partner and that partner has exactly one partner back. Or: a person and their Aadhaar number; a country and its capital.
One row on one side matches many on the other, but not the reverse. Example: one branch has many students, but each student belongs to one branch. One customer places many orders; each order has one customer.
Implemented by putting a foreign key on the "many" side (students.branch_id).
Many rows on each side match many on the other. Example: a student enrols in many courses and a course has many students. Also: teachers ↔ students, students ↔ exams.
You can't store this with a foreign key in either table. Instead you always create a third, junction table (e.g. enrollments(student_id, course_id)) that holds pairs. So an M:N relationship needs 3 tables.
Conceptual questions
These check whether you understand why, not just what. Several go one step beyond the video.
Is every candidate key a super key? Is every super key a candidate key?
Candidate keys are the minimal super keys, so they're a subset.
Why might a company choose an auto-increment emp_id over email as the primary key, even though email is unique?
Stable, short, meaningless ids make the best primary keys (surrogate keys).
A table has candidate keys {roll_no} and {aadhaar}. You pick roll_no as primary key. What is aadhaar now?
Candidate keys not chosen are alternate keys (and you'd usually still mark them UNIQUE).
Can a foreign key column contain duplicate values?
Uniqueness is required on the referenced primary key, not on the foreign key.
In a 1:1 relationship between person and passport, where should the foreign key go?
Putting it on the optional side avoids NULLs; UNIQUE keeps it truly one-to-one.