MySQL FOREIGN KEY

Creating relationships between database tables

🔗 What is FOREIGN KEY?

A FOREIGN KEY creates a link between two tables by referencing the PRIMARY KEY of another table. It maintains referential integrity, ensuring relationships between tables remain consistent and valid throughout your database. 


-- Creating tables with FOREIGN KEY relationship
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Output:

Tables created with FOREIGN KEY relationship established.

Key FOREIGN KEY Concepts

🔗

Table Relationships

Links tables together 

FOREIGN KEY (dept_id)
🛡️

Data Integrity

Prevents invalid references 

REFERENCES parent(id)
⚙️

Cascade Actions

Auto-update related data 

ON DELETE CASCADE
🔄

Referential Integrity

Maintains valid relationships 

ON UPDATE CASCADE

🔹 Creating FOREIGN KEY

Define FOREIGN KEY constraints to establish relationships between tables. The foreign key column must reference a PRIMARY KEY or UNIQUE column in the parent table, ensuring that child records always point to valid parent records. 

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Output:

✓ Tables created with FOREIGN KEY linking orders to customers

🔹 Named FOREIGN KEY Constraint

Naming FOREIGN KEY constraints makes them easier to identify and manage. Use descriptive names that indicate the relationship, which helps when debugging errors or modifying constraints later. Named constraints also appear clearly in error messages for better troubleshooting. 

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category_id INT,
    CONSTRAINT fk_product_category 
    FOREIGN KEY (category_id) 
    REFERENCES categories(category_id)
);

Output:

✓ Table created with named FOREIGN KEY constraint 'fk_product_category'

🔹 ON DELETE CASCADE

ON DELETE CASCADE automatically deletes child records when the parent record is deleted. This maintains referential integrity by preventing orphaned records. Use this when child records have no meaning without their parent, like order items without an order. 

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(100),
    quantity INT,
    FOREIGN KEY (order_id) 
    REFERENCES orders(order_id) 
    ON DELETE CASCADE
);

Output:

✓ FOREIGN KEY with CASCADE delete created. Deleting an order will delete its items.

🔹 ON UPDATE CASCADE

ON UPDATE CASCADE automatically updates foreign key values in child tables when the referenced primary key changes in the parent table. This ensures consistency across related tables without manual updates, though changing primary keys is generally discouraged in database design. 

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) 
    REFERENCES employees(emp_id) 
    ON UPDATE CASCADE
    ON DELETE SET NULL
);

Output:

✓ Self-referencing FOREIGN KEY created with CASCADE update and SET NULL delete

🔹 ON DELETE SET NULL

ON DELETE SET NULL sets the foreign key to NULL when the parent record is deleted, preserving child records while removing the broken reference. This is useful when child records should remain even after the parent is gone, like keeping employee records after department deletion. 

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) 
    REFERENCES employees(emp_id) 
    ON DELETE SET NULL
);

Output:

✓ FOREIGN KEY with SET NULL created. Projects remain if manager is deleted.

🔹 Adding FOREIGN KEY to Existing Table

Add FOREIGN KEY constraints to existing tables using ALTER TABLE. Ensure that all existing values in the foreign key column match valid values in the referenced parent table, or the constraint addition will fail with a referential integrity error. 

-- Add FOREIGN KEY to existing table
ALTER TABLE orders 
ADD CONSTRAINT fk_customer 
FOREIGN KEY (customer_id) 
REFERENCES customers(customer_id) 
ON DELETE CASCADE;

Output:

✓ FOREIGN KEY constraint added to existing table

🔹 Dropping FOREIGN KEY

Remove FOREIGN KEY constraints when you need to restructure table relationships or temporarily disable referential integrity checks. Use the constraint name to drop it, which is why naming your constraints is a best practice for easier management. 

-- Drop FOREIGN KEY constraint
ALTER TABLE orders 
DROP FOREIGN KEY fk_customer;

Output:

✓ FOREIGN KEY constraint dropped successfully

🧠 Test Your Knowledge

What does a FOREIGN KEY do?