Keys — primary, candidate, super, foreign, natural vs surrogate

1. Super Key — the most general idea

WHY this is the base concept: before we trim anything, we just ask "does this set of columns guarantee uniqueness?" If yes → super key.

2. Candidate Key — minimal super keys

WHY "minimal" matters: we want the smallest honest identifiers. From them we'll later pick one boss (the primary key).

3. Primary Key — the chosen one

WHY pick just one: a table needs a single, stable handle for relationships and indexing. Other candidate keys become alternate keys (still unique, enforced with UNIQUE, but not "the" key).

Worked example We pick

roll_no as PRIMARY KEY; email becomes an alternate key.

CREATE TABLE Student (
  roll_no INT PRIMARY KEY,        -- chosen candidate key
  email   VARCHAR(100) UNIQUE,    -- alternate key
  name    VARCHAR(50),
  dob     DATE
);

Why this step? PRIMARY KEY auto-enforces unique + not null; UNIQUE enforces uniqueness but allows NULL.

4. Foreign Key — the link between tables

WHY: relations are split across tables to avoid repetition. The foreign key is the glue.

Worked example Linking enrolments to students

CREATE TABLE Enrolment (
  enrol_id INT PRIMARY KEY,
  roll_no  INT,
  course   VARCHAR(20),
  FOREIGN KEY (roll_no) REFERENCES Student(roll_no)
);

Why this step? You cannot insert roll_no = 999 into Enrolment unless student 999 exists. Delete a referenced student → DB blocks it (or cascades) → integrity preserved.

Figure — Keys — primary, candidate, super, foreign, natural vs surrogate

5. Natural vs Surrogate Key

Aspect	Natural	Surrogate
Meaning	has business meaning	none
Stability	may change	never changes
Storage	varies (string)	small int/UUID
Readable	yes	no

Common mistake Steel-manning the classic confusions

(a) "Every super key is a candidate key." Why it feels right: both guarantee uniqueness. Fix: candidate = minimal super key. A super key with redundant columns is disqualified. Direction: every candidate is a super key, not reverse.

(b) "A primary key can be NULL like other columns." Why it feels right: a column is a column. Fix: a NULL means "unknown", so it can't identify a row → primary key must be NOT NULL by definition.

(c) "Foreign key values must be unique." Why it feels right: keys sound unique. Fix: a foreign key can repeat freely (one student has many enrolments). It's the referenced key that's unique, not the FK column.

(d) "Surrogate keys are always better." Why it feels right: they never change. Fix: you still often need a UNIQUE constraint on the natural key to stop duplicate real-world entities; otherwise two rows with same email but different id slip in.

Recall Feynman: explain to a 12-year-old (click to reveal)

Imagine a classroom. Every kid needs a way to be picked out from everyone else.

Super key = any description that points to exactly one kid — even a wordy one like "the kid with roll 7 wearing red". Extra words are fine as long as it's unique.
Candidate key = the shortest such descriptions that still work, like just "roll 7".
Primary key = the one we officially write on everyone's name tag — say the roll number.
Foreign key = when the library wants to note who borrowed a book, it writes the kid's roll number, borrowing the classroom's name-tag system.
Natural name tag = something true about them (their email). Surrogate = a random ticket number we hand out that means nothing but never changes.

Flashcards

What is a super key?

Any set of attributes whose combined values uniquely identify each row (may contain redundant attributes).

What extra property turns a super key into a candidate key?

Minimality — no proper subset of it is still a super key.

Is every candidate key a super key?

Yes. Every candidate key is a super key, but not every super key is a candidate key.

What two constraints must a primary key always satisfy?

Unique AND NOT NULL.

What is an alternate key?

A candidate key that was NOT chosen as the primary key.

What does a foreign key enforce?

Referential integrity — it can only reference values that exist in the referenced (candidate/primary) key.

Can foreign key column values repeat?

Yes — only the referenced key must be unique; the FK column itself can have duplicates and (often) NULLs.

Define a natural key.

A key made of real-world, meaningful data that already exists (e.g. email, ISBN).

Define a surrogate key.

A system-generated, meaningless, stable identifier (e.g. auto-increment id, UUID).

Why prefer surrogate over natural keys?

Natural data can change/be NULL/non-unique; a surrogate is stable, compact, always present, and never needs to change.

If {A,B} is unique and {A} alone is also unique, is {A,B} a candidate key?

No — it isn't minimal; {A} is. {A,B} is only a super key.

Difference between PRIMARY KEY and UNIQUE constraint in SQL?

PRIMARY KEY = unique + NOT NULL + one per table; UNIQUE allows NULLs and you can have many.

Connections

Relational Model — tables as sets of rows
Functional Dependencies — formal basis for "what determines what"
Normalization — keys drive 2NF/3NF/BCNF decomposition
Referential Integrity — enforced by foreign keys
Indexes — primary key usually backed by a clustered index
Entity-Relationship Model — keys map to entity identifiers

Concept Map

Hinglish (regional understanding)

Intuition Hinglish mein samjho

Dekho, ek database table basically rows ka ek set hota hai, aur set mein duplicate nahi chalte. Toh humein har row ko alag se pehchaanne ka tarika chahiye — yahi cheez key kehlaati hai. Sabse general idea hai super key: koi bhi columns ka group jiski value har row mein unique ho. Usme extra faltu column bhi ho sakte hain. Jab tum usme se faltu columns hata ke sabse chhota unique set bana lo, woh ban jaata hai candidate key (minimal). In candidate keys mein se jo ek official identifier choose karte ho — wahi primary key hai, jo hamesha unique aur NOT NULL hoti hai.

Foreign key ka kaam hai do tables ko jodna. Maan lo Student table mein roll_no primary key hai, aur Enrolment table mein bhi roll_no likha hai jo Student ko point karta hai — yeh foreign key hai. Important baat: foreign key column mein values repeat ho sakti hain (ek student ke multiple enrolments), lekin jis cheez ko woh point kar raha hai (PK) woh unique hona zaroori hai. Isse referential integrity milti hai — tum kisi aise student ko reference nahi kar sakte jo exist hi nahi karta.

Natural vs surrogate: natural key matlab real-world data jaise email ya ISBN — jiska meaning hota hai. Problem yeh ki email change ho sakta hai, ya NULL ho sakta hai. Isiliye log surrogate key use karte hain — ek auto-increment id ya UUID jiska koi business meaning nahi, par woh stable rehti hai aur kabhi change nahi karni padti. Best practice: surrogate id ko PK banao, par natural data (email) pe UNIQUE constraint zaroor lagao taaki duplicate real entities na ghusein. Yaad rakho ladder: Super → Candidate → Primary (chhoti hoti jaati hai), aur Foreign = doosri table ka reference.

Keys — primary, candidate, super, foreign, natural vs surrogate

1. Super Key — the most general idea

2. Candidate Key — minimal super keys

3. Primary Key — the chosen one

4. Foreign Key — the link between tables

5. Natural vs Surrogate Key

Flashcards

Connections

Concept Map

Hinglish (regional understanding)

Go deeper — visual, from zero

Test yourself — Databases

Connections