Skip to content

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

graph TD
    A[All Data] --> B[Super Keys]
    B --> C[Candidate Keys]
    C --> D[Primary Key]
    C --> E[Alternate Keys]

📊 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