Primary Key vs Foreign Key with School Database Example
Primary key uniquely identifies each row in its own table (like a roll number); foreign key is that value stored in another table to connect them. Complete example.
The roll-number analogy
In a school, every student gets a roll number — unique, never repeated, never blank. That is a primary key: it identifies exactly one row in its own table. Now the fees register doesn't rewrite the student's full details — it just writes the roll number next to each payment. That roll number written in another register is a foreign key: a reference that connects the two registers together.
Both keys in real tables
CREATE TABLE students (
roll_no INT PRIMARY KEY, -- primary key: unique + not null
name VARCHAR(50),
class VARCHAR(10)
);
CREATE TABLE fees (
fee_id INT PRIMARY KEY AUTO_INCREMENT,
roll_no INT, -- same value, different table
amount DECIMAL(8,2),
paid_on DATE,
FOREIGN KEY (roll_no) REFERENCES students(roll_no) -- the connection
);students fees
+---------+-------+------+ +--------+---------+---------+
| roll_no | name |class | | fee_id | roll_no | amount |
| 101 | Aman | 10 |<------| 1 | 101 | 1500.00 |
| 102 | Priya | 10 |<------| 2 | 102 | 1500.00 |
+---------+-------+------+ | 3 | 101 | 800.00 |
+--------+---------+---------+Notice: roll_no 101 appears once in students (primary key — no repeats) but twice in fees (foreign key — repeats allowed, Aman paid twice). That asymmetry is the heart of the whole concept.
What the foreign key actually protects
INSERT INTO fees (roll_no, amount) VALUES (999, 500);
-- ERROR: Cannot add or update a child row:
-- a foreign key constraint fails
-- (roll number 999 does not exist in students!)
DELETE FROM students WHERE roll_no = 101;
-- ERROR: Cannot delete or update a parent row
-- (Aman still has fee records pointing to him!)This is called referential integrity — the database physically refuses to create orphan records (a fee payment for a student who doesn't exist) or orphan children (deleting a student whose payments remain). Without the foreign key, both bad inserts would silently succeed and your data would rot.
Comparison table
| Point | Primary key | Foreign key |
|---|---|---|
| Purpose | Uniquely identify rows in own table | Connect to another table's primary key |
| Duplicates | ❌ Never | ✅ Allowed (Aman pays many times) |
| NULL | ❌ Never | ✅ Allowed (unless you forbid it) |
| How many per table | Exactly one (can be composite) | Many allowed |
| Lives in | Parent table | Child table |
| Guarantees | Row identity | Referential integrity |
Two follow-ups interviewers add
- "Can a table have two primary keys?" No — only one primary key, but it can be a composite key made of multiple columns together, like PRIMARY KEY (roll_no, exam_id) in a marks table.
- "Can a foreign key be NULL?" Yes, unless you add NOT NULL — a NULL foreign key means "no parent linked yet", like a fee record entered before assigning the student.
- Interview line: "Primary key = identity within the table; foreign key = relationship between tables, enforcing referential integrity."
Roll-number वाली analogy
School में हर student को roll number मिलता है — unique, कभी repeat नहीं, कभी खाली नहीं. यही primary key है: अपनी table में exactly एक row की पहचान. अब fees register student की पूरी details दोबारा नहीं लिखता — हर payment के आगे बस roll number लिख देता है. दूसरे register में लिखा वह roll number ही foreign key है: दोनों registers को जोड़ने वाला reference.
दोनों keys असली tables में
CREATE TABLE students (
roll_no INT PRIMARY KEY, -- primary key: unique + not null
name VARCHAR(50),
class VARCHAR(10)
);
CREATE TABLE fees (
fee_id INT PRIMARY KEY AUTO_INCREMENT,
roll_no INT, -- same value, दूसरी table
amount DECIMAL(8,2),
paid_on DATE,
FOREIGN KEY (roll_no) REFERENCES students(roll_no) -- connection
);students fees
+---------+-------+------+ +--------+---------+---------+
| roll_no | name |class | | fee_id | roll_no | amount |
| 101 | Aman | 10 |<------| 1 | 101 | 1500.00 |
| 102 | Priya | 10 |<------| 2 | 102 | 1500.00 |
+---------+-------+------+ | 3 | 101 | 800.00 |
+--------+---------+---------+ध्यान दें: roll_no 101 students में एक बार है (primary key — repeat नहीं) लेकिन fees में दो बार (foreign key — repeat allowed, Aman ने दो बार fees दी). यही asymmetry पूरे concept का दिल है.
Foreign key असल में क्या बचाती है
INSERT INTO fees (roll_no, amount) VALUES (999, 500);
-- ERROR: Cannot add or update a child row:
-- a foreign key constraint fails
-- (roll number 999 students में है ही नहीं!)
DELETE FROM students WHERE roll_no = 101;
-- ERROR: Cannot delete or update a parent row
-- (Aman के fee records अभी भी उसे point कर रहे हैं!)इसे referential integrity कहते हैं — database orphan records बनने से physically मना कर देता है (ऐसे student की fee जो exist नहीं करता) और orphan children से भी (ऐसे student को delete करना जिसकी payments बची हैं). Foreign key के बिना दोनों गलत inserts चुपचाप हो जाते और data सड़ता रहता.
Comparison table
| Point | Primary key | Foreign key |
|---|---|---|
| Purpose | अपनी table में rows की unique पहचान | दूसरी table की primary key से connection |
| Duplicates | ❌ कभी नहीं | ✅ Allowed (Aman कई बार pay करता है) |
| NULL | ❌ कभी नहीं | ✅ Allowed (जब तक आप मना न करें) |
| हर table में कितनी | Exactly एक (composite हो सकती है) | कई allowed |
| रहती कहां है | Parent table | Child table |
| Guarantee | Row identity | Referential integrity |
दो follow-ups जो interviewers जोड़ते हैं
- "क्या table में दो primary keys हो सकती हैं?" नहीं — सिर्फ एक primary key, लेकिन वह कई columns से मिलकर बनी composite key हो सकती है, जैसे marks table में PRIMARY KEY (roll_no, exam_id).
- "क्या foreign key NULL हो सकती है?" हां, जब तक NOT NULL न लगाएं — NULL foreign key का मतलब "अभी कोई parent linked नहीं".
- Interview line: "Primary key = table के अंदर identity; foreign key = tables के बीच relationship, जो referential integrity enforce करती है."
Frequently Asked Questions
What is the difference between primary key and foreign key?
A primary key uniquely identifies each row in its own table (no duplicates, no NULL); a foreign key is a column in another table that stores primary-key values to connect the tables and enforce referential integrity.
Can a foreign key have duplicate values?
Yes. Many child rows can reference the same parent — one student can have many fee payments — while the primary key side never repeats.
How many primary keys can a table have?
Exactly one, but it can be a composite key spanning multiple columns, such as PRIMARY KEY (roll_no, exam_id).