? MySQL FOREIGN KEY

MySQL Relational Integrity Constraints

⚡ Quick Overview

A FOREIGN KEY in MySQL creates a link between two tables. It ensures that the value in one table (child) must exist in another table (parent), which helps maintain referential integrity and prevents invalid or orphaned data.

? Key Concepts

Parent table: The table that contains the primary key or unique key (e.g., Customers).
Child table: The table that contains the foreign key referencing the parent (e.g., Orders).
FOREIGN KEY constraint: Ensures that a value inserted into the child column already exists in the parent column.
Referential integrity: Prevents actions that would break relationships between rows in related tables.
ON DELETE / ON UPDATE: Optional rules that control what happens when the parent row is deleted or updated.

? Syntax

The FOREIGN KEY constraint can be defined inside a CREATE TABLE statement. The referenced column in the parent table must be a PRIMARY KEY or have a UNIQUE constraint.

? View Syntax Example

-- Define a foreign key on ID referencing another table
CREATE TABLE ChildTable (
ID INT,
FOREIGN KEY (ID) REFERENCES ParentTable(ID)
);

? Example: Customers & Orders

Here, each order must belong to a valid customer. The CustomerID column in Orders references the CustomerID column in Customers.

? View Table Definitions

-- Parent table: each customer has a unique CustomerID
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100) NOT NULL
);

-- Child table: each order must reference an existing customer
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

? Example with ON DELETE / ON UPDATE

You can use ON DELETE and ON UPDATE to control what happens when the parent row is deleted or updated.

? View Cascading Example

-- Automatically delete/update child rows when the parent changes
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE,
CustomerID INT,
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
ON DELETE CASCADE
ON UPDATE CASCADE
);

? What Happens at Runtime?

If you try to insert an order with a CustomerID that does not exist in Customers, MySQL will reject the insert and throw an error.
If ON DELETE CASCADE is used and a customer is deleted, all their related orders in Orders will be deleted automatically.
If ON UPDATE CASCADE is used and a customer's CustomerID changes, the corresponding CustomerID in Orders will be updated automatically.
Without these options, MySQL may block deletes/updates on the parent table when related child rows exist.

✅ Tips & Best Practices

Always ensure the referenced column in the parent table (e.g., Customers.CustomerID) is a PRIMARY KEY or has a UNIQUE constraint.
Use appropriate ON DELETE and ON UPDATE actions:
- CASCADE for automatic propagation.
- SET NULL if you want to keep the row but remove the reference.
- RESTRICT / NO ACTION to block the change when child rows exist.
Choose foreign key names that clearly describe the relationship, e.g. fk_orders_customer.
Index foreign key columns in the child table to improve join performance.
Plan relationships before creating tables to avoid complex migrations later.

? Try It Yourself

Create two tables: Departments and Employees. Make Employees.DepartmentID a foreign key referencing Departments.DepartmentID.
Insert some departments and employees. Try inserting an employee with a non-existing DepartmentID and observe the error.
Add ON DELETE SET NULL to the foreign key and delete a department. Check the DepartmentID values in Employees.
Modify the foreign key to use ON DELETE CASCADE and see how behavior changes when you delete a department.