MySQL Foreign Key

MySQL Foreign Key Constraint

A foreign key in MySQL is a field (or collection of fields) in one table that refers to the primary key in another table. It is used to enforce referential integrity and ensure that the relationship between tables remains valid.

1. Why Use a Foreign Key?

✔ Maintains Data Integrity – Prevents invalid data by ensuring relationships between tables remain consistent.
✔ Enforces Referential Integrity – Restricts deletion or updating of referenced data if related records exist.
✔ Improves Database Organization – Defines clear relationships between tables, making queries more efficient.

2. How to Create a Foreign Key in MySQL?

A foreign key is added using the FOREIGN KEY constraint in the CREATE TABLE or ALTER TABLE statement.

Syntax:

FOREIGN KEY (column_name) REFERENCES parent_table(column_name)

Example: Creating Foreign Key in a Table

Scenario:

We have two tables:

customers (Primary table) – Contains customer details.
orders (Child table) – Stores orders placed by customers, linking to the customers table using customer_id as a foreign key.

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

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

✔ This ensures that every customer_id in orders must exist in the customers table.

3. How Foreign Keys Work?

✅ Valid Insert (Referenced Key Exists)

INSERT INTO customers (id, name) VALUES (1, 'John Doe');
INSERT INTO orders (id, customer_id, order_date) VALUES (101, 1, '2024-01-01');

✔ Allowed because customer_id = 1 exists in customers.

❌ Invalid Insert (Referenced Key Does Not Exist)

INSERT INTO orders (id, customer_id, order_date) VALUES (102, 999, '2024-01-02');

❌ Error: Cannot add or update a child row because customer_id = 999 does not exist in customers.

4. Foreign Key Constraints (ON DELETE / ON UPDATE Actions)

MySQL allows you to define what happens when the referenced key is deleted or updated.

Options:

Constraint	Description
`ON DELETE CASCADE`	Deletes child records when the parent is deleted.
`ON DELETE SET NULL`	Sets foreign key value to `NULL` when the parent is deleted.
`ON DELETE RESTRICT`	Prevents deletion of the parent if child records exist.
`ON DELETE NO ACTION`	Same as `RESTRICT`, prevents deletion.
`ON UPDATE CASCADE`	Updates child records when the parent key changes.

Example: Adding Constraints

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE
);

✔ If a customer is deleted, all their orders will also be deleted (CASCADE).
✔ If customers.id is updated, orders.customer_id will also be updated (CASCADE).

5. How to Drop a Foreign Key?

To remove a foreign key from a table, use ALTER TABLE:

Find the Foreign Key Name

SHOW CREATE TABLE orders;

Look for the foreign key name (e.g., orders_ibfk_1).

Drop the Foreign Key

ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;

6. How to Check Foreign Keys in MySQL?

To list all foreign keys in a database:

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_database_name' AND REFERENCED_TABLE_NAME IS NOT NULL;

7. Common Foreign Key Errors & Solutions

Error Message	Cause	Solution
`Cannot add or update a child row`	Trying to insert a value in the child table that doesn’t exist in the parent table.	Ensure referenced value exists in the parent table before inserting.
`Cannot delete or update a parent row`	Trying to delete a row that is referenced by a foreign key.	Use `ON DELETE CASCADE` or delete child rows first.
`Error Code: 1215 - Cannot add foreign key constraint`	Data types of the parent and child columns do not match.	Ensure both columns have the same data type and collation.

8. Summary

A foreign key links two tables and enforces referential integrity.
Use FOREIGN KEY (column) REFERENCES parent_table(column).
Use ON DELETE and ON UPDATE constraints to define behavior.
To remove a foreign key, use ALTER TABLE table_name DROP FOREIGN KEY constraint_name;.
Always check for data type mismatches when adding foreign keys.

Would you like an example of handling foreign key constraints in a real-world application? 🚀

Souy Soeng

MySQL Foreign Key