How to Use MySQL Foreign Keys for Quicker Database Development

MySQL is a fantastic open source database used by the many thousands of web applications. The default MyISAM table system is one of the simplest and fastest around, but it does not support foreign keys (although it is planned for version 6).
Fortunately, the lesser-used InnoDB table type does support foreign keys: this article will show how they could reduce your coding effort and increase data integrity.

What are Foreign Keys?

A foreign key establishes a relationship, or constraint, between two tables.
Disclaimer! For the purpose of this example, we will create two simple database tables. They are not well designed, but will demonstrate the power of foreign keys!

• employee: a table of company employees where each member is assigned a unique ID
• borrowed: a table of borrowed books. Every record will reference a borrower’s employee ID.

We will define a foreign key relationship between the employee’s ID in both tables. This provides a couple of advantages:

1. It is not possible to enter an invalid employee ID in the ‘borrowed’ table.
2. Employee changes are handled automatically by MySQL.

Creating an Example Database

Our example database is created as follows:

CREATE DATABASE mydb;
USE mydb;

We now define our two tables. Note that InnoDB is specified as the table type and we will also add an index for the employee’s last name.

CREATE TABLE employee (
 id smallint(5) unsigned NOT NULL,
 firstname varchar(30),
 lastname varchar(30),
 birthdate date,
 PRIMARY KEY (id),
 KEY idx_lastname (lastname)
) ENGINE=InnoDB;

CREATE TABLE borrowed (
 ref int(10) unsigned NOT NULL auto_increment,
 employeeid smallint(5) unsigned NOT NULL,
 book varchar(50),
 PRIMARY KEY (ref)
) ENGINE=InnoDB;

We can now specify our foreign key (this could be handled in the CREATE TABLE statement, but it is shown separately here):

ALTER TABLE borrowed 
ADD CONSTRAINT FK_borrowed 
FOREIGN KEY (employeeid) REFERENCES employee(id) 
ON UPDATE CASCADE
ON DELETE CASCADE;

This tells MySQL that we want to alter the borrowed table by adding a constraint called ‘FK_borrowed’. The employeeid column will reference the id column in the employee table – in other words, an employee must exist before they can borrow a book.
The final two lines are perhaps the most interesting. They state that if an employee ID is updated or an employee is deleted, the changes should be applied to the borrowed table.

Adding Table Data

We will now populate the tables with data. Remember that our employees must be added first:
employee:

id	firstname	lastname	birthdate
1	John	Smith	1976-01-02
2	Laura	Jones	1969-09-05
3	Jane	Green	1967-07-15

borrowed:

ref	employeeid	book
1	1	SitePoint Simply SQL
2	1	SitePoint Ultimate HTML Reference
3	1	SitePoint Ultimate CSS Reference
4	2	SitePoint Art and Science of JavaScript

The table shows that John has borrowed 3 books, Laura has borrowed 1, and Jane has not borrowed any. Standard SQL queries can be run to find useful information such as “which books has John borrowed”:

SELECT book FROM borrowed 
JOIN employee ON employee.id=borrowed.employeeid 
WHERE employee.lastname='Smith';

Result:
SitePoint Simply SQL
SitePoint Ultimate HTML Reference
SitePoint Ultimate CSS Reference

Cascading in Action

The Accounts Department calls us with a problem: Laura’s employee ID must be changed from 2 to 22 owing to a clerical error. With standard MyISAM tables, you would need to change every table that referenced the employee ID. However, our InnoDB constraints ensure that changes are cascaded following a single update:

UPDATE employee SET id=22 WHERE id=2;

If we examine our borrowed table, we will find that the update has occurred without us needing to run additional code:
borrowed:

ref	employeeid	book
1	1	SitePoint Simply SQL
2	1	SitePoint Ultimate HTML Reference
3	1	SitePoint Ultimate CSS Reference
4	22	SitePoint Art and Science of JavaScript

It is a busy day and we now have the Personnel Department on the phone. John’s learnt so much from the SitePoint books, he’s left the company to set up on his own (he was frisked at the door to ensure he returned them all). Again, we need a single SQL statement:

DELETE FROM employee WHERE id=1;

The deletion is cascaded through to our borrowed table, so all John’s references are removed:
borrowed:

ref	employeeid	book
4	22	SitePoint Art and Science of JavaScript

Although this is a simple example, it demonstrates the power of foreign keys. It is easy to retain data integrity without additional code or complex series of SQL commands. Note there are other alternatives to ‘CASCADE’ in your UPDATE and DELETE definitions:

• NO ACTION or RESTRICT: the update/delete is rejected if there are one or more related foreign key values in a referencing table, i.e. you could not delete the employee until their books had been returned.
• SET NULL: update/delete the parent table row, but set the mis-matching foreign key columns in our child table to NULL (note that the table column must not be defined as NOT NULL).

The same concepts can be applied to large-scale databases containing dozens of tables with inter-linked relationships.