Key Keepers of the Database! Solving Mysteries with Primary & Foreign Keys

What Are Keys in a Database?

A key is like an ID card for data.

• It helps identify records
• It helps connect tables
• It keeps data clean and organized

We mainly use two keys:

1. Primary Key
2. Foreign Key

---

Primary Key (The Super ID)

Imagine your school roll number:

• No two students have the same roll number
• Everyone must have one
• It never changes

That’s exactly what a Primary Key does!

---

Rules of a Primary Key

A Primary Key:
✔ Is unique (no duplicates)
✔ Is not NULL
✔ Identifies one and only one row

---

Example Table: students

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

What’s happening?

• student_id is the Primary Key
• Every student must have a unique ID

---

Wrong Example (Duplicate Primary Key)

INSERT INTO students VALUES (1, 'Amit', 10);
INSERT INTO students VALUES (1, 'Ravi', 11); -- Error

MySQL says:

“Hey! Two students can’t have the same ID!”

---

Foreign Key (The Connector)

Imagine:

• A classroom
• Many students
• The classroom number is written in every student’s notebook

That classroom number is a Foreign Key.

A Foreign Key connects one table to another table

---

Example Tables

Parent Table: classes

CREATE TABLE classes (
    class_id INT PRIMARY KEY,
    class_name VARCHAR(20)
);

Child Table: students

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

---

What’s the Connection?

• classes.class_id → Primary Key
• students.class_id → Foreign Key

This means:

A student must belong to a valid class

---

Invalid Foreign Key Example

INSERT INTO students VALUES (1, 'Rohit', 99);

Error!
Because class 99 does not exist

---

Why Are Primary & Foreign Keys Important?

Problem	Without Keys	With Keys
Duplicate data	Yes	No
Wrong relationships	Yes	No
Data confusion	High	Low
Data safety	Weak	Strong

---

Practice Questions (Try First!)

Question 1

Create a table teachers where teacher_id is a primary key.

CREATE TABLE teachers (
    teacher_id INT PRIMARY KEY,
    teacher_name VARCHAR(50)
);

---

Question 2

Create a table subjects linked to teachers.

CREATE TABLE subjects (
    subject_id INT PRIMARY KEY,
    subject_name VARCHAR(50),
    teacher_id INT,
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);

---

Question 3

Can a Foreign Key contain duplicate values?

Yes
Many students can belong to the same class.

---

Interview Questions & Answers

Q1. What is a Primary Key?

Answer:
A Primary Key uniquely identifies each row in a table and cannot be NULL or duplicated.

---

Q2. What is a Foreign Key?

Answer:
A Foreign Key creates a relationship between two tables by referring to a Primary Key in another table.

---

Q3. Can a table have multiple Primary Keys?

Answer:
No
But it can have a Composite Primary Key

---

Q4. What happens if we insert an invalid Foreign Key?

Answer:
MySQL throws an error and stops the insert to protect data integrity.

---

Q5. Why are keys important in real projects?

Answer:
They ensure:

• Data accuracy
• Proper relationships
• Reliable reports
• Safe database design

---

Final Kid-Friendly Summary

Primary Key = Roll Number (Unique Identity)
Foreign Key = Classroom Number (Connection)

Together they:

• Protect your data
• Connect tables
• Make databases smart