Using Foreign Keys in MySQL

Last week I discovered a neat little feature of MySQL I'd never used before; the ability to use foreign keys as a way to delete (or update) relational records across multiple (i.e. joined) tables. This is called a CASCADE ON DELETE and it has been available as a feature in transactional databases for quite some time. In fact, it has been available in MySQL Server since version 3.23.44. Foreign key constraints are attractive to database designers because they force degrees of referential data integrity.

There were a number things I needed to do to get this to work including changing the engine on my tables from MyISAM to InnoDB, creating indexes and setting up each key relationship. My intent was to implement these changes using phpMyAdmin, but some of the discussions and articles I came across were a bit wanting in this regard and offered little in the way of quick, decipherable information.

Consequently, I decided to create a series of video tutorials demonstrating how I eventually used phpMyAdmin to establish foreign key constraints in my database. Perhaps these little narrated clips will help a fellow "head scratcher" looking to do the same thing with data in his or her MySQL environment. I know they would have saved me time had I come across similar resources as I was doing my own investigation.

On a side note, this is the first time I've used Google video services in this capacity and I'm interested to see how it goes. I decided to use both the built-in video upload feature available in Blogger and Google video (Introduction clip only) to reach a potentially wider audience. I'm not that jazzed about the "watchability" of these videos so I made the higher quality Quicktime movies available for download, too.

Introduction:
The following preface clip introduces the use of foreign keys to CASCADE DELETE related records between parent and child tables.

Download Quicktime version (.mov 2.1 MB)

Step 1:
First, convert all MySQL tables from MyISAM to InnoDB (if needed).

Download Quicktime version (.mov 6.3 MB)

Step 2:
Next, designate indexes on appropriate fields.

Download Quicktime version (.mov 13.4 MB)


Step 3:
Finally, add foreign key relationships between parent and child tables.

Download Quicktime version (.mov 5.8 MB)

Update 5/5/08

MySQL might complain and throw a #1216 or #1452 - Cannot add or update a child row: a foreign key constraint fails SQL error similar to the following when creating foreign key relationships in EXISTING tables which have already been populated with data.

MySQL error as seen in phpMyAdmin 2.11.5.1 stating there was a foreign key constraint that failed in a child row.

Why? Because orphaned records exist in the child table which relate to a record in the parent table that has already been deleted! Fortunately, I've found some handy SQL queries using LEFT OUTER JOIN here to clean child tables and easily delete unmatched records. As always, don't forget to BACK EVERYTHING UP before attempting these queries!! First, to find "wayward" records with no matching id in a corresponding parent table:

SELECT * FROM `agenda` LEFT OUTER JOIN meetings on
agenda.meeting_id=meetings.meeting_id WHERE
meetings.meeting_id is NULL;

Then, if your query was successful and found the appropriate records:

DELETE agenda.* FROM `agenda` LEFT
OUTER JOIN  `meetings`  ON agenda.meeting_id =
meetings.meeting_id WHERE meetings.meeting_id IS NULL;