Primary & Foreign Keys 🗝️

Mentor's Note: Primary Keys and Foreign Keys are the DNA of a Relational Database. They define who everyone is and how they are related. Master this, and you can build any complex system! 💡

---

🌟 The Scenario: The School System 🏫

Imagine a school with Students and Classes.

1. Primary Key (The ID Card 🆔): Every student has a Roll Number. No two students share one, and no student is without one. It is their unique anchor.
2. Foreign Key (The Enrollment 🔗): In the 'Grades' table, we list the Roll Number. This links the grade back to the specific student in the main registry.

---

💻 1. Primary Key (The Anchor ⚓)

A Primary Key is a combination of NOT NULL and UNIQUE. It uniquely identifies each record.

Syntax

CREATE TABLE students (
    student_id INT PRIMARY KEY, -- ⚓ The Anchor
    name VARCHAR(50)
);

---

💻 2. Foreign Key (The Bridge 🌉)

A Foreign Key links a column in one table (Child) to the Primary Key of another table (Parent). This ensures Referential Integrity.

Syntax

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    -- 🌉 Building the bridge to the Customers table
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

---

🎨 Visual Logic: The Link

---

🛡️ Referential Integrity Actions

What happens if you delete a Parent row? SQL lets you choose:

1. ON DELETE CASCADE: If you delete a Customer, delete all their Orders too. 🌪️
2. ON DELETE SET NULL: If you delete a Customer, keep the Orders but set their customer_id to blank. ⚪
3. NO ACTION (Default): Prevent the deletion of the Customer if they still have Orders. 🚫

-- Example: Auto-delete orders when customer is removed
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE

---

📊 Comparison Table

Feature	Primary Key	Foreign Key
Purpose	Uniquely identify a row 👤	Link to another table 🔗
Duplicates?	❌ No	✅ Yes
NULLs?	❌ No	✅ Yes (Usually)
Limit	Only 1 per table	Multiple per table

---

📈 Learning Path

• ← Back: NOT NULL & UNIQUE
• Next: CHECK & DEFAULT →