Skip to content

Primary & Foreign Keys 🗝️

Prerequisites: CREATE TABLE, NOT NULL & UNIQUE

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)
);

erDiagram
    CUSTOMER ||--o{ ORDER : "places"
    CUSTOMER {
        int id PK
        string name
    }
    ORDER {
        int order_id PK
        date order_date
        int customer_id FK
    }

🛡️ 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