Chapter 10: Primary & Foreign Keys 🔑

🎯 Goal

By the end of this chapter, you'll understand:

What a Primary Key is
Why every table needs a Primary Key
What a Foreign Key is
Why Foreign Keys exist
How tables are connected
Parent and Child tables
Referential Integrity
What happens when keys are missing
Real-world examples

Part 1 — The Story (Learn Like a Movie)

🎬 The Kingdom's Giant Library

The kingdom had the world's largest library.

There were:

📚 2 million books
👨‍🎓 500,000 students
📖 10 million borrow records

Initially...

The librarian stored records like this:

Student Name	Book Name
Rahul	SQL Basics
Rahul	Database Design
Priya	Python
Rahul	AI for Beginners

Everything looked fine...

One Day...

A new student joined.

His name?

Rahul

Now there were two Rahuls.

The librarian became confused.

Someone asked:

"Which Rahul borrowed 'SQL Basics'?"

Librarian:

🤯

"No idea."

Next Disaster

A student changed his name.

Rahul became:

Rahul Sharma

Now every borrow record had to be updated.

There were 12,000 borrow entries for Rahul.

The librarian spent three days updating records.

Then Another Problem

Someone accidentally typed:

Rahool

instead of

Rahul

Now the system thought:

Rahul

Rahool

Rahul Sharma

were three different people.

Chaos.

Enter the Database Wizard

The wizard said,

"Stop identifying people by their names."

Everyone looked confused.

The wizard gave every student a magical ID.

Student ID	Name
101	Rahul
102	Priya
103	John
104	Rahul

Notice something?

Two Rahuls.

No problem.

Because IDs are unique.

Now borrow records became

Borrow ID	Student ID	Book
1	101	SQL Basics
2	104	Python
3	102	Java

Now the king asked:

"Which Rahul borrowed SQL Basics?"

Wizard:

Student ID = 101

Done.

The King Asked

"But how do we know Student ID 101 actually exists?"

Wizard smiled.

"That's where Foreign Keys come in."

He made another rule.

Borrow table:

Borrow ID	Student ID	Book
1	101	SQL Basics

The Student ID must exist in the Students table.

If someone tried:

Borrow ID	Student ID	Book
2	999	Python

Wizard shouted:

❌

"There is no Student 999!"

Database rejected it.

King:

"So Borrow table points to Students table?"

Wizard:

"Exactly."

Another Example

Amazon

Customers

Customer ID	Name
1	Rahul
2	Priya

Orders

Order ID	Customer ID
1001	1
1002	2
1003	1

Order doesn't store:

Rahul
Rahul
Rahul
Rahul
Rahul

Instead:

Customer ID = 1

Less storage.

No spelling mistakes.

Faster searching.

Easy updates.

King finally understood.

"Primary Keys identify."

"Foreign Keys connect."

Wizard smiled.

"Exactly."

What Actually Happened?

Every table should have a way to uniquely identify each row.

That unique identifier is called a Primary Key.

Other tables use that value to create relationships.

That reference is called a Foreign Key.

What is a Primary Key?

A Primary Key is a column (or set of columns) whose value uniquely identifies every row in a table.

Example

Students

Student_ID	Name
1	Rahul
2	Priya
3	John

Here,

Student_ID

is the Primary Key.

Because every value is unique.

Rules of a Primary Key

A Primary Key must:

✅ Be unique

✅ Never be NULL

✅ Identify exactly one row

Wrong Example

ID
1
1
2

❌ Duplicate value.

Wrong Example

ID
1
NULL
2

❌ NULL not allowed.

Creating a Primary Key

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

Now student_id must always be unique.

Real-Life Primary Keys

Hospital

Patient_ID

Bank

Account_Number

Passport Office

Passport_Number

University

Roll_Number

Company

Employee_ID

Why Not Use Name?

Names can:

Repeat
Change
Be misspelled

IDs don't.

What is a Foreign Key?

A Foreign Key is a column that refers to the Primary Key of another table.

It creates a relationship.

Example

Students

Student_ID	Name
1	Rahul
2	Priya

Borrow

Borrow_ID	Student_ID
100	1
101	2

Borrow table doesn't know names.

It only stores Student_ID.

Visualizing the Relationship

Students Table

Student_ID (PK)
      │
      │
      ▼
Borrow Table

Student_ID (FK)

Think of the Foreign Key as a bridge connecting two tables.

Creating a Foreign Key

CREATE TABLE borrow (
    borrow_id INT PRIMARY KEY,
    student_id INT,
    book_name VARCHAR(100),

    FOREIGN KEY (student_id)
    REFERENCES students(student_id)
);

Now every student_id in borrow must exist in students.

Parent Table vs Child Table

Students

↓

Borrow

Students = Parent

Borrow = Child

Because Borrow depends on Students.

Referential Integrity

Big name.

Simple idea.

It means:

Relationships between tables should always remain valid.

Example

Students

Student_ID
1
2

Borrow

Student_ID
1

Valid.

Invalid

Borrow

Student_ID
999

Student 999 doesn't exist.

Database rejects it.

That's referential integrity.

Why Foreign Keys Matter

Imagine Amazon.

Without Foreign Keys:

Orders

Order	Customer_ID
1	99999

Customer doesn't exist.

But order exists.

Impossible situation.

Foreign Keys prevent this.

Can One Primary Key Have Many Foreign Keys?

Absolutely.

Customer

Customer_ID
1

Orders

Customer_ID
1

Reviews

Customer_ID
1

Wishlist

Customer_ID
1

One customer can have many related records.

Primary Key vs Foreign Key

Primary Key	Foreign Key
Uniquely identifies a row.	References another table's Primary Key.
Must be unique.	Can repeat.
Cannot be NULL.	Can be NULL (unless restricted).
One per table (commonly).	A table can have many Foreign Keys.

Common Mistakes

Mistake 1

Using Name as Primary Key

Rahul
Rahul
Rahul

❌ Names aren't guaranteed to be unique.

Mistake 2

Foreign Key without Parent

Orders

Customer_ID = 999

Customer doesn't exist.

Database rejects it (if the constraint exists).

Mistake 3

Changing Primary Key values often

Primary Keys should remain stable.

If IDs keep changing, all related Foreign Keys must also change.

Real-Life Examples

YouTube

Users

User_ID

Videos

User_ID

Foreign Key connects the video to its uploader.

Instagram

Users

↓

Posts

↓

Comments

↓

Likes

Everything is connected using keys.

Banking

Customers

↓

Accounts

↓

Transactions

Each transaction belongs to an account, and each account belongs to a customer.

Hospital

Patients

↓

Appointments

↓

Prescriptions

↓

Bills

Each table is connected through keys.

Part 2 — Question & Answer (Progressive Learning)

Q1. What is a Primary Key?

Answer: A Primary Key is a column (or combination of columns) that uniquely identifies each row in a table.

Q2. Why do we need a Primary Key?

Answer: It ensures that every record can be uniquely identified, avoiding confusion caused by duplicate or changing values like names.

Q3. Can two rows have the same Primary Key?

Answer: No. A Primary Key must always be unique.

Q4. Can a Primary Key contain `NULL`?

Answer: No. Every row must have a valid Primary Key value.

Q5. Why is using a name as a Primary Key a bad idea?

Answer: Because names can be duplicated, changed, or misspelled. IDs are stable and unique.

Q6. What is a Foreign Key?

Answer: A Foreign Key is a column in one table that references the Primary Key of another table, creating a relationship between the two tables.

Q7. Why do we use Foreign Keys?

Answer: They keep related data connected and prevent invalid references, ensuring data consistency.

Q8. What is referential integrity?

Answer: Referential integrity ensures that every Foreign Key value points to an existing Primary Key, preventing broken relationships between tables.

Q9. What are parent and child tables?

Answer: The table containing the Primary Key is called the parent table. The table containing the Foreign Key is called the child table because it depends on the parent.

Q10. Give a real-life example of Primary and Foreign Keys.

Answer: In an online shopping system, customers.customer_id is the Primary Key. The orders.customer_id column is a Foreign Key that links each order to the customer who placed it.

Part 3 — Top 5 MCQs

1. What is the main purpose of a Primary Key?

A. Store duplicate values

B. Uniquely identify each row

C. Store passwords

D. Sort data

✅ Answer: B

2. Which statement about a Primary Key is correct?

A. It can contain duplicate values.

B. It can contain NULL.

C. It must be unique and cannot be NULL.

D. It is optional for every table.

✅ Answer: C

3. What does a Foreign Key do?

A. Encrypts data

B. References the Primary Key of another table

C. Deletes duplicate rows

D. Sorts the table

✅ Answer: B

4. Which of the following best demonstrates a Foreign Key?

A. students.student_id

B. orders.order_id

C. orders.customer_id referencing customers.customer_id

D. products.price

✅ Answer: C

5. What happens if you try to insert an invalid Foreign Key value (assuming the constraint is enforced)?

A. The database automatically creates the missing parent row.

B. The row is inserted with a warning.

C. The database rejects the insert because the referenced row doesn't exist.

D. The Foreign Key is converted to NULL.

✅ Answer: C

🧠 Chapter 10 Summary

A Primary Key (PK) uniquely identifies every row in a table.
A Primary Key must be unique and cannot be NULL.
A Foreign Key (FK) references a Primary Key in another table.
Foreign Keys create relationships between tables and enforce referential integrity.
The parent table contains the Primary Key, while the child table contains the Foreign Key.
Nearly every real-world relational database—banks, hospitals, e-commerce platforms, schools, and social media—uses Primary and Foreign Keys to keep data connected and consistent.

Memory Trick:
🔑 Primary Key = "Who am I?" (identity)
🔗 Foreign Key = "Who am I related to?" (relationship)

Chapter 10: Primary & Foreign Keys 🔑

🎯 Goal

Part 1 — The Story (Learn Like a Movie)

🎬 The Kingdom's Giant Library

One Day...

Next Disaster

Then Another Problem

Enter the Database Wizard

The King Asked

Another Example

What Actually Happened?

What is a Primary Key?

Rules of a Primary Key

Creating a Primary Key

Real-Life Primary Keys

Why Not Use Name?

What is a Foreign Key?

Visualizing the Relationship

Creating a Foreign Key

Parent Table vs Child Table

Referential Integrity

Why Foreign Keys Matter

Can One Primary Key Have Many Foreign Keys?

Primary Key vs Foreign Key

Common Mistakes

Mistake 1

Mistake 2

Mistake 3

Real-Life Examples

YouTube

Instagram

Banking

Hospital

Part 2 — Question & Answer (Progressive Learning)

Q1. What is a Primary Key?

Q2. Why do we need a Primary Key?

Q3. Can two rows have the same Primary Key?

Q4. Can a Primary Key contain NULL?

Q5. Why is using a name as a Primary Key a bad idea?

Q6. What is a Foreign Key?

Q7. Why do we use Foreign Keys?

Q8. What is referential integrity?

Q9. What are parent and child tables?

Q10. Give a real-life example of Primary and Foreign Keys.

Part 3 — Top 5 MCQs

1. What is the main purpose of a Primary Key?

2. Which statement about a Primary Key is correct?

3. What does a Foreign Key do?

4. Which of the following best demonstrates a Foreign Key?

5. What happens if you try to insert an invalid Foreign Key value (assuming the constraint is enforced)?

🧠 Chapter 10 Summary

Q4. Can a Primary Key contain `NULL`?