🛡️ Lesson 12: Foreign Keys & Referential Integrity

What Is Referential Integrity?

Referential integrity means every foreign key value in a child table must match an existing primary key value in the parent table (or be NULL, if the FK allows NULLs). In plain English: you can't reference something that doesn't exist.

Without Foreign Key Constraints

Without constraints, MySQL happily accepts bad data:

-- Without a FK constraint, this succeeds even though customer 999 doesn't exist!
INSERT INTO orders (customer_id, order_date, total)
VALUES (999, '2026-04-15', 29.99);
-- No error — but now you have an "orphan" order pointing to nobody
                

With Foreign Key Constraints

With constraints, MySQL blocks invalid references:

-- With a FK constraint, this FAILS:
INSERT INTO orders (customer_id, order_date, total)
VALUES (999, '2026-04-15', 29.99);
-- ERROR 1452: Cannot add or update a child row:
-- a foreign key constraint fails
                

Creating Foreign Key Constraints

Inline Syntax (During CREATE TABLE)

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10, 2) DEFAULT 0,

    -- Foreign key constraint
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);
                

Named Constraints

Giving your constraints explicit names makes them easier to manage later:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10, 2) DEFAULT 0,

    -- Named foreign key constraint
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(id)
);
                

Requirements for Foreign Keys

Both the FK column and the referenced column must:

ON DELETE — What Happens When You Delete a Parent

The big question: what should MySQL do when you try to delete a customer who has orders? You have four choices:

RESTRICT (The Default)

Blocks the delete entirely if any child rows exist:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE RESTRICT
);

-- If customer 1 has orders:
DELETE FROM customers WHERE id = 1;
-- ERROR 1451: Cannot delete or update a parent row:
-- a foreign key constraint fails
                

This is the safest option — it forces you to deal with the child rows first.

CASCADE — Delete Everything

Automatically deletes all child rows when the parent is deleted:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE CASCADE
);

-- Deleting customer 1 ALSO deletes all their orders automatically
DELETE FROM customers WHERE id = 1;
-- Customer gone, all their orders gone too
                

SET NULL — Preserve the Child, Break the Link

Keeps the child rows but sets their FK column to NULL:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,   -- Must allow NULL for SET NULL to work!
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE SET NULL
);

-- Deleting customer 1 sets their orders' customer_id to NULL
DELETE FROM customers WHERE id = 1;
-- Customer gone, but orders still exist with customer_id = NULL
                

NO ACTION

In MySQL, NO ACTION is functionally identical to RESTRICT — it blocks the delete. The distinction matters in other databases that support deferred constraint checking, but in MySQL they behave the same way.

Comparison

ON UPDATE — What Happens When You Change a Parent's Key

ON UPDATE controls what happens when you change a parent's primary key value. The options are the same as ON DELETE:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE    -- if customer.id changes, update all orders too
);
                

Combining ON DELETE and ON UPDATE

-- A practical combination
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    book_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    price_at_time DECIMAL(6, 2) NOT NULL,

    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE       -- delete items when order is deleted
        ON UPDATE CASCADE,

    CONSTRAINT fk_items_book
        FOREIGN KEY (book_id) REFERENCES books(id)
        ON DELETE RESTRICT      -- can't delete a book that's been ordered
        ON UPDATE CASCADE
);
                

Notice how different relationships use different rules: order items should disappear with their order (CASCADE), but you shouldn't be able to delete a book that appears in past orders (RESTRICT).

Choosing the Right Action

Here's a decision framework for choosing your ON DELETE action:

Common Patterns

Adding & Dropping Foreign Keys

You can add foreign keys to existing tables with ALTER TABLE:

Adding a Foreign Key

-- Add a FK to an existing table
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;
                

-- Find orphan orders
SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;

-- Fix them (delete or update) before adding the constraint
DELETE FROM orders WHERE customer_id NOT IN (SELECT id FROM customers);
                    

Dropping a Foreign Key

-- Drop by constraint name
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;

-- If you also want to drop the index MySQL created:
ALTER TABLE orders DROP INDEX fk_orders_customer;
                

Changing a Foreign Key's Rules

You can't directly modify an FK constraint — you have to drop and recreate it:

-- Step 1: Drop the old constraint
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;

-- Step 2: Add it back with new rules
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE       -- changed from RESTRICT to CASCADE
    ON UPDATE CASCADE;
                

Temporarily Disabling FK Checks

For bulk data operations, you can temporarily disable FK checking:

-- Disable FK checks
SET FOREIGN_KEY_CHECKS = 0;

-- Perform bulk operations...
-- (imports, migrations, table restructuring)

-- Re-enable FK checks
SET FOREIGN_KEY_CHECKS = 1;
                

Viewing Foreign Key Information

Several ways to see what foreign keys exist on a table:

SHOW CREATE TABLE

SHOW CREATE TABLE orders;
-- Shows the full CREATE TABLE including all constraints
                

Information Schema

-- List all FKs for a specific table
SELECT
    CONSTRAINT_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'bookstore'
    AND TABLE_NAME = 'orders'
    AND REFERENCED_TABLE_NAME IS NOT NULL;
                

+---------------------+-------------+-----------------------+------------------------+
| CONSTRAINT_NAME     | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+---------------------+-------------+-----------------------+------------------------+
| fk_orders_customer  | customer_id | customers             | id                     |
+---------------------+-------------+-----------------------+------------------------+
                    

Checking ON DELETE/ON UPDATE Rules

-- See the actual rules for each FK
SELECT
    CONSTRAINT_NAME,
    DELETE_RULE,
    UPDATE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'bookstore'
    AND TABLE_NAME = 'orders';
                

+---------------------+-------------+-------------+
| CONSTRAINT_NAME     | DELETE_RULE | UPDATE_RULE |
+---------------------+-------------+-------------+
| fk_orders_customer  | RESTRICT    | CASCADE     |
+---------------------+-------------+-------------+
                    

Troubleshooting FK Errors

Foreign key errors are among the most common MySQL frustrations. Here's how to handle each one:

Error 1452: Cannot Add or Update a Child Row

-- ERROR 1452: Cannot add or update a child row:
-- a foreign key constraint fails

-- Cause: You're inserting a FK value that doesn't exist in the parent table
INSERT INTO orders (customer_id, order_date) VALUES (999, '2026-04-15');
-- Fix: Use a customer_id that actually exists
INSERT INTO orders (customer_id, order_date) VALUES (1, '2026-04-15');
                

Error 1451: Cannot Delete or Update a Parent Row

-- ERROR 1451: Cannot delete or update a parent row:
-- a foreign key constraint fails

-- Cause: You're deleting a parent that has children (RESTRICT is active)
DELETE FROM customers WHERE id = 1;
-- Fix: Delete or reassign children first
DELETE FROM orders WHERE customer_id = 1;
DELETE FROM customers WHERE id = 1;
                

Error 1215: Cannot Add Foreign Key Constraint

-- ERROR 1215: Cannot add foreign key constraint

-- Common causes:
-- 1. Data type mismatch
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id);
-- customer_id is INT but customers.id is INT UNSIGNED → FAIL

-- 2. Referenced column isn't indexed
-- (Primary keys are automatically indexed — this happens with non-PK references)

-- 3. Referenced table doesn't exist yet
-- Fix: Create parent table first

-- 4. Different character sets/collations on the columns
-- Fix: Ensure both use the same charset
                

Debugging with SHOW ENGINE

-- Get detailed FK error information
SHOW ENGINE INNODB STATUS;
-- Look for the "LATEST FOREIGN KEY ERROR" section
                

Exercises

CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department_id INT,
    CONSTRAINT fk_employees_department
        FOREIGN KEY (department_id) REFERENCES departments(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE assignments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    project_name VARCHAR(200) NOT NULL,
    CONSTRAINT fk_assignments_employee
        FOREIGN KEY (employee_id) REFERENCES employees(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);
                        

-- 1. Add the FK
ALTER TABLE books
    ADD CONSTRAINT fk_books_author
    FOREIGN KEY (author_id) REFERENCES authors(id)
    ON DELETE RESTRICT;

-- 2. Drop it
ALTER TABLE books DROP FOREIGN KEY fk_books_author;

-- 3. Make column nullable and re-add with SET NULL
ALTER TABLE books MODIFY author_id INT NULL;

ALTER TABLE books
    ADD CONSTRAINT fk_books_author
    FOREIGN KEY (author_id) REFERENCES authors(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE;
                        

Summary

Quick Reference

📚 Additional Resources

• MySQL Docs — Foreign Key Constraints
• MySQL Docs — InnoDB Foreign Key Constraints
• MySQL Docs — foreign_key_checks Variable

🚀 What's Next?

You've built the relationships and enforced them with constraints. Now it's time to use them! In the next lesson, you'll learn JOINs — the SQL technique for querying data across multiple related tables in a single statement. INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, and self-joins — the most powerful (and most-asked-about-in-interviews) topic in SQL.