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

4.4.2 · Coding › Databases

1. Super Key — sabse general idea

WHY yeh base concept hai: kuch bhi trim karne se pehle, hum bas poochte hain "kya yeh columns ka set uniqueness guarantee karta hai?" Agar haan → super key.

2. Candidate Key — minimal super keys

WHY "minimal" matter karta hai: hum sabse chhote honest identifiers chahte hain. Unhi mein se hum baad mein ek boss (primary key) chunenge.

3. Primary Key — chuna hua ek

WHY sirf ek chunna: ek table ko relationships aur indexing ke liye ek single, stable handle chahiye. Baaki candidate keys alternate keys ban jaati hain (abhi bhi unique, UNIQUE se enforce hoti hain, lekin "the" key nahi).

Worked example Hum

roll_no ko PRIMARY KEY chunte hain; email alternate key ban jaata hai.

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

Yeh step kyun? PRIMARY KEY automatically unique + not null enforce karta hai; UNIQUE uniqueness enforce karta hai lekin NULL allow karta hai.

4. Foreign Key — tables ke beech link

WHY: relations ko repetition se bachne ke liye tables mein split kiya jaata hai. Foreign key woh glue hai.

Worked example Enrolments ko students se link karna

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

Yeh step kyun? Aap Enrolment mein roll_no = 999 insert nahi kar sakte jab tak student 999 exist nahi karta. Referenced student ko delete karo → DB isko block karta hai (ya cascade karta hai) → integrity preserved.

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

5. Natural vs Surrogate Key

Aspect	Natural	Surrogate
Meaning	business meaning hai	koi nahi
Stability	change ho sakti hai	kabhi nahi badalti
Storage	varies (string)	small int/UUID
Readable	haan	nahi

Common mistake Classic confusions ko steel-man karna

(a) "Har super key ek candidate key hoti hai." Kyun sahi lagta hai: dono uniqueness guarantee karte hain. Fix: candidate = minimal super key. Redundant columns wali super key disqualify ho jaati hai. Direction: har candidate ek super key hai, reverse nahi.

(b) "Primary key doosre columns ki tarah NULL ho sakti hai." Kyun sahi lagta hai: column toh column hota hai. Fix: NULL matlab "unknown", toh yeh ek row ko identify nahi kar sakta → primary key by definition NOT NULL honi chahiye.

(c) "Foreign key values unique honi chahiye." Kyun sahi lagta hai: keys unique lagte hain. Fix: foreign key freely repeat ho sakti hai (ek student ke bahut saare enrolments hain). Referenced key unique hoti hai, FK column nahi.

(d) "Surrogate keys hamesha better hoti hain." Kyun sahi lagta hai: yeh kabhi nahi badalte. Fix: natural key par aapko phir bhi aksar ek UNIQUE constraint chahiye hoti hai taaki duplicate real-world entities rok sakein; warna same email lekin alag id wali do rows ghus jaati hain.

Recall Feynman: ek 12-saal ke bacche ko explain karo (click to reveal)

Ek classroom socho. Har bacche ko aise identify karna hai ki baaki sabse alag ho.

Super key = koi bhi aisi description jo exactly ek bacche ko point kare — chahe wordy ho jaise "roll 7 wala baccha jo red pehen raha hai". Extra words theek hain jab tak unique ho.
Candidate key = woh sabse chhoti descriptions jo abhi bhi kaam karti hain, jaise bas "roll 7".
Primary key = woh ek jo hum officially sabke name tag par likhte hain — maano roll number.
Foreign key = jab library note karna chahti hai kisne book li, toh woh bacche ka roll number likhti hai, classroom ke name-tag system ko borrow karke.
Natural name tag = unke baare mein koi sachchi cheez (unka email). Surrogate = ek random ticket number jo hum dete hain jo kuch matlab nahi rakhta lekin kabhi nahi badalti.

Flashcards

Super key kya hota hai?

Attributes ka koi bhi aisa set jiske combined values har row ko uniquely identify karte hain (redundant attributes ho sakte hain).

Kaun si extra property ek super key ko candidate key banati hai?

Minimality — uska koi bhi proper subset abhi bhi super key nahi hota.

Kya har candidate key ek super key hoti hai?

Haan. Har candidate key ek super key hoti hai, lekin har super key candidate key nahi hoti.

Primary key ko hamesha kaun se do constraints satisfy karne chahiye?

Unique AND NOT NULL.

Alternate key kya hota hai?

Woh candidate key jo primary key ke roop mein choose nahi ki gayi.

Foreign key kya enforce karta hai?

Referential integrity — yeh sirf wahi values reference kar sakta hai jo referenced (candidate/primary) key mein exist karti hain.

Kya foreign key column values repeat ho sakti hain?

Haan — sirf referenced key unique honi chahiye; FK column khud duplicates aur (aksar) NULLs rakh sakta hai.

Natural key define karo.

Woh key jo real-world, meaningful data se bani hoti hai jo already exist karti hai (jaise email, ISBN).

Surrogate key define karo.

System-generated, meaningless, stable identifier (jaise auto-increment id, UUID).

Natural keys ke over surrogate kyun prefer karte hain?

Natural data change ho sakta hai/NULL ho sakta hai/non-unique ho sakta hai; surrogate stable, compact, hamesha present hota hai, aur kabhi change nahi karna padta.

Agar {A,B} unique hai aur {A} akela bhi unique hai, toh kya {A,B} ek candidate key hai?

Nahi — yeh minimal nahi hai; {A} hai. {A,B} sirf ek super key hai.

SQL mein PRIMARY KEY aur UNIQUE constraint mein kya fark hai?

PRIMARY KEY = unique + NOT NULL + table mein ek; UNIQUE NULLs allow karta hai aur aapke paas bahut saare ho sakte hain.

Connections

Relational Model — rows ke sets ke roop mein tables
Functional Dependencies — "kya kya determine karta hai" ka formal basis
Normalization — keys 2NF/3NF/BCNF decomposition drive karte hain
Referential Integrity — foreign keys se enforce hoti hai
Indexes — primary key usually ek clustered index se backed hoti hai
Entity-Relationship Model — keys entity identifiers se map hoti hain