Foreign Keys: Enforcing Relationships and Referential Integrity

Primary keys identify rows uniquely, but foreign keys connect tables together. They're the glue that makes relational databases work, ensuring that relationships between tables remain valid and consistent. A foreign key in one table references a primary key in another table, creating an enforceable link. In this lesson, you'll learn how foreign keys maintain data integrity, prevent orphaned records, and make your database relationships bulletproof.

📚 What You'll Learn

• What foreign keys are and how they work
• How foreign keys enforce referential integrity
• How foreign keys create relationships between tables
• The difference between parent and child tables
• What happens when you try to violate foreign key constraints
• Introduction to ON DELETE and ON UPDATE behaviors
• How to create foreign keys in SQL
• Common foreign key mistakes and how to avoid them
• Best practices for using foreign keys

1. What is a Foreign Key?

Visual Example: Students and Enrollments

The student_id column in enrollments is a foreign key that references the student_id primary key in the students table. This creates a relationship: each enrollment belongs to exactly one student.

2. What is Referential Integrity?

Foreign Keys Prevent Invalid Data

❌ Without Foreign Keys (Disaster Waiting to Happen)

-- No foreign key constraint
INSERT INTO enrollments (student_id, course_code, grade)
VALUES (999999, 'CS101', 'A');

-- ✓ Insert succeeds... but student 999999 doesn't exist! 😱
-- This is an "orphaned record" - an enrollment with no student!

Problem: Now you have an enrollment record for a student that doesn't exist. Who got the grade? Where do you send the transcript? The data is meaningless!

✅ With Foreign Keys (Protected)

-- With foreign key constraint
INSERT INTO enrollments (student_id, course_code, grade)
VALUES (999999, 'CS101', 'A');

-- ✗ ERROR: Cannot add or update a child row: 
-- a foreign key constraint fails
-- Student 999999 doesn't exist, so insert is REJECTED ✓

3. Parent Tables vs. Child Tables

The Relationship

4. How Foreign Keys Protect Your Data

Protection 1: Cannot Insert Invalid References

-- ❌ This will fail
INSERT INTO enrollments (student_id, course_code)
VALUES (888888, 'CS101');

-- Error: student 888888 doesn't exist in students table

Protection 2: Cannot Delete Referenced Parents

-- ❌ This will fail if student 100234 has enrollments
DELETE FROM students 
WHERE student_id = 100234;

-- Error: Cannot delete - student has enrollments referencing them
-- This prevents orphaned enrollments!

Protection 3: Cannot Update Primary Keys That Are Referenced

-- ❌ This will fail if student 100234 is referenced elsewhere
UPDATE students 
SET student_id = 200000 
WHERE student_id = 100234;

-- Error: Cannot update - enrollments still reference 100234
-- This is why primary keys should NEVER change!

5. Creating Foreign Keys in SQL

Method 1: Define at Table Creation (Column Level)

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_code VARCHAR(20) NOT NULL,
    grade VARCHAR(2),
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

Method 2: Define at Table Level (Named Constraint)

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_code VARCHAR(20) NOT NULL,
    grade VARCHAR(2),
    CONSTRAINT fk_enrollment_student 
        FOREIGN KEY (student_id) 
        REFERENCES students(student_id)
);

Method 3: Add to Existing Table

-- Add foreign key constraint after table creation
ALTER TABLE enrollments
ADD CONSTRAINT fk_enrollment_student
FOREIGN KEY (student_id) REFERENCES students(student_id);

Multiple Foreign Keys in One Table

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    grade VARCHAR(2),
    -- Two foreign keys!
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

6. Introduction to ON DELETE and ON UPDATE

You can control what happens when a parent record is deleted or updated. Here are the basic options:

Example: CASCADE Delete

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_code VARCHAR(20) NOT NULL,
    grade VARCHAR(2),
    FOREIGN KEY (student_id) 
        REFERENCES students(student_id)
        ON DELETE CASCADE  -- If student deleted, delete their enrollments too
);

Example: SET NULL

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,  -- Can be NULL
    order_date DATE NOT NULL,
    total DECIMAL(10, 2),
    FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE SET NULL  -- If customer deleted, keep order but set customer to NULL
);

7. Common Foreign Key Mistakes

8. 🎯 Practice Exercises

-- Create parent table first
CREATE TABLE authors (
    author_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    country VARCHAR(100)
);

-- Create child table with foreign key
CREATE TABLE books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    author_id INT NOT NULL,  -- Required relationship
    publication_year INT,
    CONSTRAINT fk_book_author
        FOREIGN KEY (author_id) 
        REFERENCES authors(author_id)
);

-- Test: Insert author first, then book
INSERT INTO authors (name, country) 
VALUES ('J.K. Rowling', 'United Kingdom');

INSERT INTO books (title, author_id, publication_year)
VALUES ('Harry Potter', 1, 1997);

-- This will FAIL (author 999 doesn't exist):
-- INSERT INTO books (title, author_id) VALUES ('Test Book', 999);

-- Assume customers and employees tables already exist

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    employee_id INT NOT NULL,
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    total_amount DECIMAL(10, 2) NOT NULL,
    
    -- Two foreign keys
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id),
    
    CONSTRAINT fk_order_employee
        FOREIGN KEY (employee_id) 
        REFERENCES employees(employee_id)
);

-- Example insert (requires valid customer and employee IDs):
-- INSERT INTO orders (customer_id, employee_id, total_amount)
-- VALUES (1001, 2001, 149.99);

-- Categories table
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE
);

-- Posts table with optional category
CREATE TABLE blog_posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    category_id INT,  -- NULL allowed (optional)
    published_date DATE DEFAULT CURRENT_DATE,
    
    CONSTRAINT fk_post_category
        FOREIGN KEY (category_id) 
        REFERENCES categories(category_id)
        ON DELETE SET NULL  -- Keep post, remove category reference
);

-- Example scenario:
-- INSERT INTO categories (name) VALUES ('Technology');
-- INSERT INTO blog_posts (title, content, category_id) 
-- VALUES ('AI Trends', 'Article about AI...', 1);

-- If you delete the Technology category:
-- DELETE FROM categories WHERE category_id = 1;
-- The post remains, but category_id becomes NULL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    manager_id INT,  -- References another row in THIS table
    
    CONSTRAINT fk_employee_manager
        FOREIGN KEY (manager_id) 
        REFERENCES employees(employee_id)
        ON DELETE SET NULL  -- If manager deleted, employee remains
);

-- Example: Insert CEO first (no manager)
INSERT INTO employees (name, email, manager_id)
VALUES ('Alice CEO', 'alice@company.com', NULL);

-- Insert manager who reports to CEO
INSERT INTO employees (name, email, manager_id)
VALUES ('Bob Manager', 'bob@company.com', 1);

-- Insert employee who reports to Bob
INSERT INTO employees (name, email, manager_id)
VALUES ('Carol Employee', 'carol@company.com', 2);

-- Query to see organizational structure:
SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

📝 Key Takeaways

• Foreign keys create and enforce relationships between tables
• Foreign keys reference primary keys in another table (or the same table)
• Referential integrity ensures relationships remain valid automatically
• Parent tables contain primary keys; child tables contain foreign keys
• Foreign keys prevent orphaned records (children without parents)
• Cannot insert invalid foreign key values (must reference existing parent)
• Cannot delete/update parents if children reference them (without CASCADE)
• ON DELETE CASCADE automatically deletes children with parent (use carefully!)
• ON DELETE SET NULL keeps children but removes parent reference
• Foreign keys and primary keys must have identical data types
• Always create parent tables before child tables
• Use descriptive constraint names: fk_childtable_parenttable