mySQL Foreign Keys

I love database relationships, and msSQL makes them extremely easy to manipulate at will.  When i switched back to mySQL a couple of years ago I also made the switch from phpMyAdmin to Navicat and immediately didn’t understand Navicat’s way of handling relationships in mySQL… correctly called “foreign keys” for mySQL vs. msSQL “relationships”.  My copy of Ben Forta’s MySQL Crash Course didn’t dive much more than a sentence into the subject, so I thought I’d revisit mySQL relationships when I was ready to sit down and figure it out.  And although I don’t consider foreign keys/relationships absolutely necessary because I can do everything manually, they sure are nice, particularly on the larger databases where sometimes you may overlook cleaning everything up.  Having the database do a little more work vs. the language (ColdFusion or PHP in my case) is always nice too.I really don’t want to go much into what foreign keys are or what they do aside from the fact that thy are primary key columns in tables that are linked to another table’s primary key column, and that relationship can permit actions to happen to that table as a result of another table changing. Confused?  Well if you have a table of users (all with a userID primary key column) and another table of userPhotos (each photo will have its own photoID column as a primary key, but also a column for the userID (doesn’t have to be a primary key, but it can be) so you know who the photo belongs to), you can set up the foreign key in userPhotos to automatically delete all the rows related to the userID if the user/row gets removed from the database instead of having to identify and write that query yourself (if you even remembered to do it in the first place… I always forget).  Pretty cool huh?  Anyway, this post is really intended to show how to setup foreign keys, not so much exactly what they are, when or even why to use them.  There are plenty of articles on Google that will give more in depth and better descriptions than I could offer.

So I’m going to use Navicat’s way of doing things sorta of as a fill-in-the-blank reference.  When designing a table in Navicat, click the “Foreign Keys” tab and fill out the fields:

Name – This is primarily what confused the hell out of me because msSQL relationships don’t require this (or it creates it on its own).  Anyway, this is a unique name [to the database, not the server] you are going to give the foreign key.  I don’t know if there’s a proper method of going about this, but I prefix all of mine with “fk_” so it looks something like “fk_profileID”… I can immediately look at and identify it as a foreign key.
Fields – I’ve only ever used one per row in Navicat, although it seems you can have multiple fields here, but this is the field in this selected table that you want to relate/link to another field.  It doesn’t have to be a primary key like the other field, but it does have to be the same Type and Length.
Reference Database – The database you’re going to get the primary key column from.  Typically this is going to be the same database you’re using, but you can get fancy and perform actions across different databases.
Reference Table – Select the table in the database you’re going to get the primary key from.
Reference Fields – The matching/linked field to what you selected in the Fields column above.
On Delete/On Update – This is obviously tells the key what to do when either of these actions are performed, but here’s what each of them are:

  • RESTRICT – Essentially ignores the action and does nothing.  This is the default if you leave the field(s) blank.
  • NO ACTION – Will not change anything… leaving the field in tact.  Exactly the same thing as RESTRICT.
  • CASCADE – Performs the action (deletes or updates the field respectively).
  • SET NULL – Will set the field to NULL on action.  The better question is probably whether you allow NULL or not for the column.

If you want to test the foreign key to make sure everything went right, open up the table to edit the field and see if there’s a “…” button in the field when you click to edit it.  It’s verifying the linked table to ensure the key exists before it’s created!  Not exactly a trivial process, but a very handy bit of knowledge nonetheless.

Leave a Reply

Your email address will not be published. Required fields are marked *