How to Use MySQL Foreign Keys for Quicker Database Development

Share this article

This article was written in 2009 and remains one of our most popular posts. If you’re keen to learn more about MySQL and database development, you may find this more recent article of great interest.
MySQL foreign keysMySQL 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:
idfirstnamelastnamebirthdate
1JohnSmith1976-01-02
2LauraJones1969-09-05
3JaneGreen1967-07-15
borrowed:
refemployeeidbook
11SitePoint Simply SQL
21SitePoint Ultimate HTML Reference
31SitePoint Ultimate CSS Reference
42SitePoint 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:
refemployeeidbook
11SitePoint Simply SQL
21SitePoint Ultimate HTML Reference
31SitePoint Ultimate CSS Reference
422SitePoint 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:
refemployeeidbook
422SitePoint 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.

Frequently Asked Questions about MySQL Foreign Keys for Quicker Database Development

What is the purpose of using foreign keys in MySQL?

Foreign keys in MySQL are used to establish a link between the data in two tables. They are a fundamental part of database relationships, particularly in relational databases. The primary purpose of using foreign keys is to ensure data integrity and consistency. By defining foreign key constraints, you can enforce certain rules on the data in your tables, preventing any changes that would lead to inconsistencies or violations of the defined relationships.

How do I create a foreign key in MySQL?

Creating a foreign key in MySQL involves using the FOREIGN KEY constraint during the creation of a table. The syntax is as follows:

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... )
REFERENCES parent_table (column1, column2, ... )
);
In this syntax, fk_column is the name of the foreign key, table_name is the name of the table you want to create, and parent_table is the table you want to link to.

Can a table have multiple foreign keys in MySQL?

Yes, a table can have multiple foreign keys in MySQL. Each foreign key is defined with a separate FOREIGN KEY constraint and can reference a different parent table or the same parent table but different columns.

What happens if a foreign key constraint is violated in MySQL?

If a foreign key constraint is violated in MySQL, the database system will prevent any changes that would cause the violation. This could be an attempt to insert a row with a foreign key that does not exist in the referenced table, or an attempt to delete or update a row that is referenced by a foreign key in another table.

How can I disable a foreign key constraint in MySQL?

You can disable a foreign key constraint in MySQL using the ALTER TABLE command with the DISABLE KEYS option. However, this should be done with caution as it can lead to data inconsistencies.

How do I drop a foreign key in MySQL?

To drop a foreign key in MySQL, you use the ALTER TABLE command with the DROP FOREIGN KEY clause. The syntax is as follows:

ALTER TABLE table_name
DROP FOREIGN KEY fk_name;
In this syntax, table_name is the name of the table where the foreign key is defined, and fk_name is the name of the foreign key you want to drop.

Can a foreign key be NULL in MySQL?

Yes, a foreign key can be NULL in MySQL. This represents the absence of a relationship rather than a relationship with a particular row in the parent table.

How do I view foreign keys in a MySQL table?

You can view the foreign keys in a MySQL table using the SHOW CREATE TABLE command. This will display the SQL statement that was used to create the table, including the foreign key constraints.

What is the difference between a primary key and a foreign key in MySQL?

A primary key is a column or a set of columns that uniquely identifies each row in a table. A foreign key, on the other hand, is a column or a set of columns that is used to establish a link between the data in two tables.

How do I update a foreign key in MySQL?

To update a foreign key in MySQL, you first need to drop the existing foreign key using the ALTER TABLE command with the DROP FOREIGN KEY clause, and then add the new foreign key using the ALTER TABLE command with the ADD FOREIGN KEY clause.

Craig BucklerCraig Buckler
View Author

Craig is a freelance UK web consultant who built his first page for IE2.0 in 1995. Since that time he's been advocating standards, accessibility, and best-practice HTML5 techniques. He's created enterprise specifications, websites and online applications for companies and organisations including the UK Parliament, the European Parliament, the Department of Energy & Climate Change, Microsoft, and more. He's written more than 1,000 articles for SitePoint and you can find him @craigbuckler.

Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week
Loading form