Primary and Foreign Keys

The foundation of relational database design — primary keys identify rows uniquely, foreign keys create relationships between tables, and together they enforce data integrity.

Primary Key vs Foreign Key - The Relationship Builders

🎯 Challenge 4: The Student ID Mystery

Scenario: You're organizing a school database. How do you ensure:

• Each student is unique?
• You can find any student quickly?
• You can link students to their classes?
• You can link students to their grades?

Think about it: What system would you use to identify and connect students?

The Answer: Keys!

---

🏆 Primary Key: The Unique Identifier

Real-world parallel: Your Social Security Number, Passport Number, or Student ID

Definition: A column (or set of columns) that uniquely identifies each row in a table.

Rules for Primary Keys:

1. ✅ Must be UNIQUE (no duplicates)
2. ✅ Cannot be NULL (must have a value)
3. ✅ Should never change
4. ✅ One per table

Example: STUDENTS table

-- Sample data:

Why is student_id the primary key?

• 🆔 Unique: No two students have same ID
• 🚫 Not null: Every student must have an ID
• 🔒 Stable: ID never changes even if name or email changes
• ⚡ Fast: Database automatically indexes primary keys

Bad primary key choices:

• ❌ Name: Not unique (many "John Smith"s exist)
• ❌ Email: Could change when student graduates
• ❌ Phone number: Can change
• ❌ Age: Definitely changes!

---

🔗 Foreign Key: The Relationship Connector

Real-world parallel: A reference number on a package pointing to your address

Definition: A column that references the primary key of another table, creating a relationship.

Example: ENROLLMENTS table

Visual relationship:

Alice (student_id: 1001) is enrolled in: → CS101 (via enrollment_id 1) → MATH200 (via enrollment_id 2)

CS101 has these students: → Alice (student_id: 1001) → Bob (student_id: 1002)

---

🎮 Interactive Comparison Game

Match the key type to its role:

Think about each one...

Answers:

1. Primary Key - Uniquely identifies each person
2. Foreign Key - Links order to a customer
3. Primary Key - Uniquely identifies each order
4. Foreign Key - Links order item to a product

---

🛡️ Foreign Key Constraints: Protecting Data Integrity

What Foreign Keys Prevent:

Scenario 1: Orphaned Records

Try to insert enrollment for non-existent student

-- Student 9999 doesn't exist!-- Result: ❌ ERROR: Foreign key constraint violated!-- Database says: "Can't enroll a student that doesn't exist!

Scenario 2: Broken References -- Try to delete a student who has enrollments

-- Result: ❌ ERROR: Foreign key constraint violated

-- Database says: "Can't delete student with existing enrollments!"

-- (You'd have orphaned enrollments pointing to non-existent student)Options for handling deletions:

-- CASCADE: Delete enrollments when student is deleted

FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE SET NULL: Set student_id to NULL in enrollments FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE SET NULL; -- RESTRICT: Prevent deletion (default) FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE RESTRICT;

Mental model: Foreign keys are like safety ropes in mountain climbing. They ensure you can't fall off a cliff (create invalid references) and keep everything connected safely!

---

📊 Complete Example: E-Commerce Database

CUSTOMERS table

ORDERS table

ORDER_ITEMS table

PRODUCTS table

The relationships:

---

💡 Quick Comparison Summary

Mental model:

• Primary Key = Your name on your ID badge
• Foreign Key = Your name on your visitor's guest list (referencing you)

---

Key Takeaways

1. Primary keys uniquely identify each row in a table — must be unique, non-null, and ideally immutable
2. Foreign keys create relationships between tables — enforcing referential integrity so orphaned records can't exist
3. Use auto-incrementing integers or UUIDs as primary keys — UUIDs are better for distributed systems, integers are better for performance
4. Foreign key constraints prevent data inconsistency — you can't delete a user if their orders still reference them