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:
- It is not possible to enter an invalid employee ID in the ‘borrowed’ table.
- 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.
Related posts:
- MySQL: the Pros and Cons of InnoDB Tables Craig discusses why the lesser-used MySQL InnoDB table engine might...
- MySQL: the Pros and Cons of MyISAM Tables MySQL offers several database table types, but which is suitable...
- MySQL Cross-Platform Table Naming Craig highlights the biggest potential pitfall of porting your MySQL-based...
- Free Performance with MySQL Table Types Last week, SitePoint launched its second Twitaway, giving away a...
- How to Administer a Remote MySQL Database using SSH Tunneling Configuring remote MySQL databases with the command line or phpMyAdmin...
I use MyISAM table types. This has something to do with InnoDB. The writer should have explained a bit about the table types.
March 12th, 2009 at 2:32 am
Perhaps I’ll write another article about the differences, pros, and cons of both table types.
March 12th, 2009 at 2:56 am
This should be great. Now you have an idea for your next article and you’ll get at least two happy readers.
March 12th, 2009 at 3:59 am
Make that 3. I am also interested in reading the differences in the 2 engines.
March 12th, 2009 at 5:04 am
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!
March 12th, 2009 at 6:07 am
small correction:
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
March 12th, 2009 at 6:33 am
Thanks for the article. Good to know about this feature.
(I think you should correct ‘John’ or ‘Jim’)
March 12th, 2009 at 11:36 am
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.
March 12th, 2009 at 11:39 am
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.
March 12th, 2009 at 2:40 pm
Nice Example and Article
March 12th, 2009 at 5:02 pm
Thanks for all the feedback, everyone. Watch this space for more MySQL articles next week.
March 12th, 2009 at 6:08 pm
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.”
March 12th, 2009 at 7:58 pm
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!
March 12th, 2009 at 8:17 pm
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!!
March 13th, 2009 at 6:24 am
thanks a lot for this information.
March 13th, 2009 at 8:33 pm
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.
March 14th, 2009 at 12:11 am
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
March 14th, 2009 at 1:55 am
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.
March 19th, 2009 at 12:07 am
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!
March 19th, 2009 at 10:18 pm
@hellomoto
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.
March 20th, 2009 at 1:13 am
both tables have to be innodb in order for foreign keys to work
[insert smiley here]
March 20th, 2009 at 1:41 am
@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.
March 20th, 2009 at 9:56 am
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.
March 21st, 2009 at 2:54 am
actually, yes, mysql does let you change the table type quite easily
;o)
March 21st, 2009 at 3:23 am
…but only if you’re not using InnoDB-specific features such as foreign keys. You must remove the constraints to change the table type.
March 23rd, 2009 at 9:24 pm
Legit article. This is one of those that will be a turning point for me. Thanks, great examples!
April 1st, 2009 at 12:12 pm