How to create and enforce foreign key relationships in MySQL

Foreign Key Relationships and Considerations

Foreign key constraint defines how referential integrity is enforced between two tables. It relates two tables together by common columns in a relational database. If a foreign key in the referencing table (foreign key table) is not null, it must match a primary key in the referenced table (primary key table). This means that relationship between the primary key and the foreign key must always be maintained so that we will never have orphaned records in the referencing table.

How foreign key relationship enforces referential integrity

We use two examples to demonstrate the concept of foreign key relationship.

Example One: foreign key relationship between two tables

CategoryID (foreign key) in Products table references to CategoryID (primary key) in Categories table.

Categories table - CateoryID as primary key:
Primary key in categories table

Products table - CategoryID as foreign key:
Foreign key in products table

Integrity enforcement #1:
The record for CategoryID 1 (Beverages category) in Categories table can't be deleted because there are related records in the Products table (product Chai and Chang). It's also can't be updated without updating CategoryID for product Chai and Chang in Products table first.

Integrity enforcement #2:
Adding a new record in Products table with CategoryID 9 is not possible becaues CategoryID 9 does not exist in Categories table. To add this new record, a new category with CategoryID value 9 must be added to Categories table first.

Note that if CategoryID in Products table allows NULL, a new product with NULL CategoryID can be added to Products table.

Example Two: self-referencing foreign key relationship

Foreign key relationship can be created between two or more columns in the same table. For example, Employees table defines foreign key relationship between EmployeeID and ReportsTo column.

Apparently, without looking at the Title column in the table below, we can tell Andrew Fuller (EmployeeID 2) is the boss. This is because his ReportsTo data is NULL which means he reports to no one.

Nancy Davollo reports to Andrew Fuller as her ReportsTo value is 2 which is Andrew Fuller's EmployeeID.

Ann Dodsworth reports to Steven Duchanan as her ReportsTo value is 5 which is Steven Duchanan's EmployeeID.

Self referencing foreign key relationship in employees table

Integrity enforcement #1:
The record for Steven Duchanan (EmployeeID 5) in Employees table can't be deleted because there are three employees report to him - Anne, Michael, Rebort. It's also can't be updated to something else without first re-assigning Anne, Michael, and Rebort to report to another employee.

Integrity enforcement #2:
Changing ReportsTo value to 10 for Nancy Davolio in Employees table is not possible becaues an employee with EmployeeID 10 does not exist yet. The same holds true when adding a new employee with a non-existing ReportsTo value. What we need to do is add a new employee with EmployeeID 10 to the table before we can refer to it.

To sum up, if a foreign key in the referencing table (foreign key table) is not null, it must match a primary key in the referenced table (primary key table). This means that relationship between the primary key and the foreign key must always be maintained so that we will never have orphaned records in the referencing table.

To maintain the foreign key and primary key relationship, a database engine needs to make sure the following two scenarios will never happen:

1. A row/record in primary key table can't be deleted if it still has related records in the foreign key table. It's also true that the primary key can't be updated if there are related records in the foreign key table.

For example, customer Wilman Kala (primary key CustomerID WILMK) can't be deleted from Customers table because the customer has placed 9 orders.

The record in foreign key table is also known as child record. The record in primary key table is also known as master record. Database engines allow you to define how you want to update the foreign key in child records if you really need to update the primary key in master record.

2. When the foreign key value is not null for a record, the record can't be inserted into the foreign key table if it does not have matching record in primary key table.

For example, we can't add an order to the Orders table if there is no customer exists for this order.

Where to find more info about foreign key relationships

Refer to article Make northwind a true relational database in MySQL for more practical info about:

The terms commonly used when designing foreign key relationships
How to create foreign key relationships in SQLyog
Considerations when creating foreign key relationships in MySQL

Other tutorials in this category

1. How to Design Relational Database

2. Enforce Data Integrity by Database Constraints

3. How to Enforce Data Type Constraint

4. How to Enforce Default Constraint and Nullability Constraint

5. Primary Key Constraint and Unique Constraint

Back to Tutorial Index Page

	Custom Search