K.I.S.S. - Using Foreign Keys in MySQL

by Ligaya Turmelle (2008-03-24)
 

No, foreign keys aren't from Brazil or Italy or even the US. Though they can be a bit strange to those who do not understand them, have no fear. We are here to teach you how to talk to them so they can serve your whims.

No, foreign keys aren't from Brazil or Italy or even the US. Though they can be a bit strange to those who do not understand them, have no fear. We are here to teach you how to talk to them so they can serve your whims. So what are foreign keys exactly?

Not to put too fine a point on it, they are what make a relational database “relational.” They are the links between tables that keeps everything connected. They are what allows you to put a customer in one table and their order in another and the products they are ordering in a third table so the database has minimal data redundancies. And the fewer redundancies you have, the better your chances of maintaining your data integrity (2 or more pieces of data being inconsistent with each other is always a bad thing).

Our Example

Ok time for an example. This example was made for MySQL; if you are using a different database, be sure to see your documentation on the various parts we will be going over.

  1. CREATE TABLE usr (
  2.   usr_id  int auto_increment not null,
  3.   first  varchar(25) not null,
  4.   surname  varchar(50) not null,
  5.   PRIMARY KEY(usr_id)
  6. ) ENGINE=InnoDB CHARACTER SET=UTF8;
  8. CREATE TABLE product (
  9.   prod_id  int auto_increment not null,
  10.   name  varchar(40) not null,
  11.   descr  varchar(255) not null,
  12.   PRIMARY KEY(prod_id)
  13. ) ENGINE=InnoDB CHARACTER SET=UTF8;
  15. CREATE TABLE invoice (
  16.   inv_id  int auto_increment not null,
  17.   usr_id  int not null,
  18.   prod_id  int not null,
  19.   quantity int not null,
  20.   PRIMARY KEY(inv_id)
  21. ) ENGINE=InnoDB CHARACTER SET=UTF8;

Above is a simple ERD (Entity Relationship Diagram) and the beginning CREATE statements for the tables we will be working on. As it sits right now, the tables have no connectivity between themselves other than through column names and any logic provided by the application itself.

Could we have put all the information into one table? Sure. The table could have columns for all the users' information, the product information and the quantity of each product being ordered. But what happens if we have a typo in the description of the product or a user's name in one of the records? Oh no, now things won't match! Will a query pull up the records with the typos along with the other records? What if it doesn't? We have bad data! The sky is falling!

Adding the Foreign Keys

Now it is time to make the connections between the tables so our application doesn't have to. We want to have the usr_id and prod_id columns in the invoice table connect to corresponding columns in the usr and product tables.

You can do this using ALTER for the invoice table given above but I prefer to think of these things while I am designing my original database (whenever I can) so I can add it to my CREATE statements.

  1. CREATE TABLE invoice (
  2.   inv_id  int auto_increment not null,
  3.   usr_id  int not null,
  4.   prod_id  int not null,
  5.   quantity int not null,
  6.   PRIMARY KEY(inv_id),
  7.   FOREIGN KEY (usr_id) REFERENCES usr(usr_id),
  8.   FOREIGN KEY (prod_id) REFERENCES product(prod_id)
  9. ) ENGINE=InnoDB CHARACTER SET=UTF8;

Note that in the new invoice table CREATE statement, I have added the syntax FOREIGN KEY (<some name>) REFERENCES table(<column>) to make the connections between the tables.

By just declaring the foreign key, we now have some built in protection for our data integrity. If we try to INSERT or UPDATE one of our foreign key values in invoice, the database will automatically check the table it connects to and make sure the value already exists in that table. If the value does not exist in the referenced table, the database will reject the INSERT/UPDATE and protect your data for you.

No longer will the application need to check the “parent” table to make sure the value is there before inserting the value into the “child” table. Feel free to delete it out; the database has your back. If you want to handle the new error a specific way, add that. Less coding is always a good thing for lazy coders.

Turning up the Power

Ready to add the more power to your foreign keys? Yep they can do even more for you.

As it stands right now, we have protection if we try to do things to the “child” table, but what if things happen in the “parent” table? How can we make sure the “child” table keeps up with any changes in the “parent” table?

MySQL allows you to have some control over the “child” table when the rows in the “parent” table are updated or deleted using the ON UPDATE and ON DELETE subclauses. (See your database's documentation to learn what subclauses it may support with foreign keys. Not all databases support all the actions and options presented.) MySQL supports 5 different actions that can be applied to the ON UPDATE and/or ON DELETE clauses.

  • CASCADE: if the “parent” table's linked row is updated/deleted we want the “child” table's columns to also be updated/deleted. What happens to the “parent”, happens to the “child”. It should be noted that you can put yourself into a cascading loop so pay attention!
  • SET NULL: if the “parent” table's linked row is updated/deleted we want the “child” table's columns to be set to NULL. This of course can only be used on columns that allow NULL values (can't be NOT NULL).
  • NO ACTION: See RESTRICT
  • RESTRICT: if the “parent” table's linked row is updated/deleted with an entry already/still in the “child” table, the database will not allow it to touch the parent table. Denied! Both NO ACTION and RESTRICT is the equivalent to not having the ON UPDATE or ON DELETE subclause for the foreign key.
  • SET DEFAULT: this is currently recognized by the parser but the InnoDB engine does nothing with it.

For my example database I decided that for the foreign keys in the invoice table, an update action would cascade to the “child” table, and a delete action would be strictly controlled for both the usr and product tables. This way, any changes to the usr or product table are automatically reflected in the invoice table, but as long as there is a product ordered or the user has an invoice, it cannot be deleted.

Below is the new CREATE statement for the invoice table with foreign keys and ON UPDATE and ON DELETE clauses.

  1. CREATE TABLE invoice (
  2.   inv_id  int auto_increment not null,
  3.   usr_id  int not null,
  4.   prod_id  int not null,
  5.   quantity int not null,
  6.   PRIMARY KEY(inv_id),
  7.   FOREIGN KEY (usr_id) REFERENCES usr(usr_id)
  8.     ON UPDATE CASCADE
  9.     ON DELETE RESTRICT,
  10.   FOREIGN KEY (prod_id) REFERENCES product(prod_id)
  11.     ON UPDATE CASCADE
  12.     ON DELETE RESTRICT
  13. ) ENGINE=InnoDB CHARACTER SET=UTF8;

Conclusion

So there you have it; foreign keys done simple. Wasn't so very hard now was it? By placing this logic in the database with the data, we no longer need to have it in our applications, thus saving us from wasting time writing code (that potentially may not protect our data integrity since we are human after all), giving us more time to talk on IRC or play Guitar Hero. After all, we know what is really important.

So keep it simple stupid! Don't make your life harder by trying to program it. Let the database do it for you.

(* All the ERD diagrams were made using MySQL's Workbench. You can find it at http://dev.mysql.com/downloads/workbench/5.0.html.)

Ligaya Turmelle is a full-time goddess, occasional PHP programmer and obsessive world traveler. Actively involved with the PHP community as a founding Principal of phpwomen.org, moderator at codewalkers.com, blogger, and long time busybody of #phpc on freenode, she hopes to one day meet all the people she talks to. Less importantly she is a co-author of “The PHP Anthology: 101 Essential Tips, Tricks & Hacks, 2nd Edition” by sitepoint publishing. Ligaya Turmelle is a Zend Certified Engineer and MYSQL Core certified.

File under: art  databases  foreign keys  homepage  mysql  php 
 

Comments

Re: K.I.S.S. - Using Foreign Keys in MySQL by billkarwin (2008-04-09 20:54:56 (America/Toronto)) 
Nice article, this is something everyone who works with databases should know about.  Unfortunately some frameworks ignore referential integrity constraints.

As a matter of trivia, the "relational" in RDBMS actually isn't about relationships between tables, though it's totally natural to make that assumption.  It refers to "relations" - which are basically tables.  The similarity to the word "relationship" is coincidental AFAIK.
Re: K.I.S.S. - Using Foreign Keys in MySQL by Mathew (2008-04-24 16:18:32 (America/Toronto)) 
Hi,

What program are you using the display the databases?

Kind Regards,
-Mathew
Re: K.I.S.S. - Using Foreign Keys in MySQL by Gav (2008-05-02 10:03:56 (America/Toronto)) 
It looks like MySQL Workbench is being used to display the diagram.
Visit the forum