How to Use MySQL Foreign Keys for Quicker Database Development

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:

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.

If you enjoyed reading this post, you’ll love Learnable; the place to learn fresh skills and techniques from the masters. Members get instant access to all of SitePoint’s ebooks and interactive online courses, like PHP & MySQL Web Development for Beginners.

Comments on this article are closed. Have a question about MySQL? Why not ask it on our forums?

Win an Annual Membership to Learnable,

SitePoint's Learning Platform

  • khuramyz

    I use MyISAM table types. This has something to do with InnoDB. The writer should have explained a bit about the table types.

  • http://www.optimalworks.net/ Craig Buckler

    Perhaps I’ll write another article about the differences, pros, and cons of both table types.

  • nachenko

    This should be great. Now you have an idea for your next article and you’ll get at least two happy readers.

  • http://www.mikedesign.net/ mauteri

    Make that 3. I am also interested in reading the differences in the 2 engines.

  • http://www.whatstyle.net Ize

    Very interesting article, Craig! I’ve only recently started working with more advanced SQL concepts such as triggers and stored procedures and am starting to see the true power of SQL.
    This little gem I did not know of. In fact, with my limited knowledge I would probably have written a trigger for this, so it’s good to know it’s much, much easier than that!

  • http://r937.com/ r937

    small correction:

    NO ACTION or RESTRICT: the update/delete is rejected if there are one or more related foreign key values in a referencing (not referenced) table

    the referenced table, of course, is the one with the PK being updated or deleted, while the referencing table is the one with the FK

  • glenngould

    Thanks for the article. Good to know about this feature.

    (I think you should correct ‘John’ or ‘Jim’)

  • Guwapo

    InnoDB is more advance in business transaction rather than MyISAM, but I would love to hear more on that, count on me to read this article.

  • http://www.mikehealy.com.au cranial-bore

    Nice overview Craig. I was aware that MySQL could enforce FK relationships, but I’ve never implemented them myself. I would normally have built the logic into the application and probably used transactions to keep things intact. I’ll try to have the DB look after FKs next time.

  • loganathan

    Nice Example and Article

  • http://www.optimalworks.net/ Craig Buckler

    Thanks for all the feedback, everyone. Watch this space for more MySQL articles next week.

  • http://rollerscapes.net s.stok

    PostgreSQL supports both Foreign Keys and FULLTEXT Search on all tables!

    Foreign Keys are not made to make things easier but to make sure the data remains consistent! you don’t want to remove an customer if the webhosting account depends on it.

    You can check this in the application it self but if you forget it you got a problem.

    “MySQL is critical in the same sense like a nuclear reactor that goes critical. Try something that really works, like PostgreSQL.”

  • http://www.optimalworks.net/ Craig Buckler

    FKs help to ensure data integrity as discussed, but that in itself is powerful, e.g. if I delete my user account, all my data is automatically deleted throughout the database. Few MySQL applications make use of that. The article was really to demonstrate that MySQL does support FKs if you don’t stick with the default MyISAM table type.

    PostgreSQL certainly has its advantages and disadvantages compared to MySQL, but I’d rather not get into that debate here!

  • http://www.lunadesign.org awasson

    Nice article Craig and it could be one of my recent favorites. It will definitely change the way I approach db development for custom web apps :)

    PS: Rudy’s new book just arrived this morning… Bring on the SQL!!

  • chandu

    thanks a lot for this information.

  • thanks very much

    I, too, would be interested in an article that further explores the differences between MyISAM and innoDB.

    I am about to start working heavily with MySQL in a project I am doing at home (my plan to take over the world). I will absolutley be using this information.

    Thanks again.

  • hellomoto

    thank you for article.
    i have question. what if may borrowed table is InnoDb, and employee is MyIsam,does mysql automaticly update/delete related tables?
    thanks

  • fsdavis

    What are the disadvantages of postgres? By happenstance I started using it instead of mysql about 12 years ago. Of course, I’ve used mysql a lot since that time as well and for simple things I use sqlite. I’ve also (briefly) used MS SQL Server, Sybase and Oracle. Postgres is basically an open source Oracle and has always had good data integrity including foreign keys. It hasn’t been pain-free, but it has been much less painful that mysql even when using the innodb engine. Lack of standards support, the fact that one must specify a db engine just to obtain foreign key constraints, and mysql’s tendency to quietly change your data makes me think of it as a toy. If you don’t need those things, you might as well use sqlite which is much easier to configure. :-)

    Performance is almost always the indictment against postgres, but the tests I’ve seen are made using mysql without innodb, so of course mysql will be faster if there is no foreign key checking! So the biggest disadvantage I’ve seen is that mysql is for some reason more popular in spite of it’s shortcomings, so more open source projects support mysql. But this doesn’t matter when you’re building a custom app.

    I don’t want to start a flame war, either. But I want to mention this, because it looks like there are a lot of folks commenting here that might benefit from knowing more about the available tools. Most people that get started with bashing postgres or mysql don’t know one or the other. I know them both quite well, and for my purposes, I use postgres for “important” apps, sqlite for everything else, and mysql only when I have to.

  • http://www.optimalworks.net/ Craig Buckler

    Off the top of my head, confusing things about PostgreSQL include a lack of native auto-incrementing fields (you need a function) and not being able to easily insert a new field between two others. It’s also less popular, not as widely available, and has fewer online resources.

    That said, there is nothing wrong with using Postgres. Nor is there anything wrong with using MySQL. They are just different database engines; both have good points and neither is perfect.

    Would anyone be interested in further PostgreSQL articles? Let’s have a vote!

  • http://www.optimalworks.net/ Craig Buckler

    @hellomoto

    what if may borrowed table is InnoDb, and employee is MyIsam, does mysql automatically update/delete related tables?

    It’s an interesting thought and I suspect nothing will occur – but I need to test that theory! You can mix and match table types within a single database though.

  • http://r937.com/ r937

    both tables have to be innodb in order for foreign keys to work

    [insert smiley here]

  • Robert Treat

    @craigbuckler just to clarify…
    “Off the top of my head, confusing things about PostgreSQL include a lack of native auto-incrementing fields (you need a function)”
    This isn’t exactly true. You can define your tables with the serial data type, which is a psuedo dataype which handles the autoincrement bits for you automagically. It’s built on top of Postgres’ sequence support, so it has some differences under the hood from mysqls auto-increment (mostly the trade off is mysql makes it easier to do gapless sequences, and postgres’s sequences scale better).
    “and not being able to easily insert a new field between two others.”
    I think you mean “add a new column to a table between two other columns”, which is true. There has been some discussion for adding this functionality, but so far there hasn’t been enough demand for someone to do the work. Ambitious developers should see http://wiki.postgresql.org/wiki/Alter_column_position#Adding_alter_column_syntax_into_postgres for more info.
    HTH.

  • http://www.optimalworks.net/ Craig Buckler

    both tables have to be innodb in order for foreign keys to work

    Yes – I’ve just tested it. In fact, MySQL won’t let you add the FK constraint or change the table type, so it’s not possible to test anyway!

    @Robert Treat
    Thanks for the information about PostgreSQL.

  • http://r937.com/ r937

    actually, yes, mysql does let you change the table type quite easily

    ;o)

  • http://www.optimalworks.net/ Craig Buckler

    mysql does let you change the table type quite easily

    …but only if you’re not using InnoDB-specific features such as foreign keys. You must remove the constraints to change the table type.

  • brianblocker

    Legit article. This is one of those that will be a turning point for me. Thanks, great examples!

  • Norman Bird

    Thanks for clarifying this ability in a clear manner. You are a great teacher and I look forward to more of your articles.

  • Death_11990

    thanks! ….very good article, good examples

  • Anonymous

    thank you, nice and easy!

  • Boltzmann

    This is a very wonderful article. It has not been up to a year when I started using mysql. Did it because it seems to be popular n I learnt it alongside php. Ever since I’ve found mysql to be very interesting that I tend to forget how to use MS SQLSERVER.
    This article helped me to learn about foreign keys. Though I’ve been hearing about it bt never cared to learn it until I needed it. Well, I’ll ve to go to mysql documentation site for more info. Thanks again for d intro

  • Randika prasad

    This should be great.This article helped me to learn about foreign keys. Though I’ve been hearing about it bt never cared to learn it until I needed it. Well, I’ll ve to go to mysql documentation site for more info. Thanks again and again.