Composite & Alternate Keys 🗝️🗝️

Mentor's Note: In simple tables, one ID is enough. But in the real world, identity can be complex. Sometimes it takes two people to open a safe—and sometimes it takes two columns to identify a row. 💡

---

🌟 The Scenario: The Hotel Room 🏨

Imagine you are managing a massive hotel.

1. Composite Key: A 'Room Number' isn't enough because there is a Room 101 on Floor 1 and a Room 101 on Floor 2. To find the exact room, you need (Floor + Room).
2. Alternate Key: Every guest has a 'Guest ID' (Primary Key), but they also have a 'Passport Number'. The Passport Number could have been the ID, but we chose Guest ID instead.

---

💻 1. The Composite Key

A primary key that consists of two or more columns.

Syntax

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enroll_date DATE,
    -- ⚓ Combined Anchor
    PRIMARY KEY (student_id, course_id)
);

Why use it? To prevent a student from enrolling in the same course twice.

---

📖 2. Candidate & Alternate Keys

Candidate Key

Any column (or set of columns) that could be a Primary Key (it's unique and not null).

• Examples: Email, Aadhar Card No, Passport No.

Alternate Key

A Candidate Key that was not chosen to be the Primary Key. We still want to keep it unique!

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,      -- ⚓ Chosen PK
    email VARCHAR(100) UNIQUE,   -- ✨ Alternate Key
    aadhaar_no VARCHAR(12) UNIQUE -- ✨ Alternate Key
);

---

🎨 Visual Logic: The Key Hierarchy

---

📊 Summary of Advanced Terms

Term	Meaning	Analogy
Super Key	Any set of columns that identifies a row.	Any combo including your Roll No.
Candidate Key	Minimal set of unique columns.	Your Roll No OR your Email.
Composite Key	PK made of 2+ columns.	(Floor + Room Number).
Surrogate Key	An artificial ID (1, 2, 3...).	A token number at a bank.

---

📈 Learning Path

• ← Back: CHECK & DEFAULT
• Next: SQL Joins →