By Michael Marr
Expert Author
Article Date: 2010-08-03
In relational database design, we rely on keys in one table to relate to the primary keys of another. The InnoDB engine takes this concept and supplies the Foreign Key constraint to keep our relationships healthy and strong. It's truly a relationship miracle-worker. Eat your heart out, Dr. Phil.
We might have some tables that look something like this:
mysql>DESCRIBE widgets;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| widgetid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(55) | NO | | NULL | |
| task | tinytext | NO | | NULL | |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql>DESCRIBE widget_parts;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| partid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| widgetid | int(10) unsigned | NO | | NULL | |
| name | varchar(55) | NO | | NULL | |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
In this example, we have the widget_parts table relating to the widgets table. As parts are added to the widget_parts table, they are related to a particular widget via the widgetid column. For example:
mysql>SELECT * FROM widgets;
+----------+------------------------+----------------+
| widgetid | name | task |
+----------+------------------------+----------------+
| 7 | Bender's Widget | hate humans |
+----------+------------------------+----------------+
| 12 | Dr. Phil's Widget | watch Dr. Phil |
+----------+------------------------+----------------+
| 51 | Nicholas Cage's widget | be boring |
+----------+------------------------+----------------+
1 row in set (0.00 sec)
mysql>SELECT * FROM widget_parts WHERE widgetid=12;
+--------+----------+------------+
| partid | widgetid | name |
+--------+----------+------------+
| 92 | 12 | television |
| 107 | 12 | earplugs |
| 132 | 12 | blindfold |
| 197 | 12 | vodka |
+--------+----------+------------+
3 rows in set (0.00 sec)
However, let's say we do the following:
mysql>DELETE FROM widgets WHERE task='watch Dr. Phil';
Thankfully, we can easily delete the widget that might watch Dr. Phil on our behalf. We wouldn't want to miss ANY episodes. However, now we have a small problem with our widget_parts table. All of the parts that were set aside to make the Dr. Phil widget work are now orphaned, and still exist in the widgets_part table.
If we were using InnoDB Foreign Keys, this could be handled much more gracefully. First, we would need to define the Foreign Key at creation:
CREATE TABLE widget_parts (
partid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
widgetid INT(10) UNSIGNED NOT NULL,
name varchar(55) NOT NULL,
PRIMARY KEY(`partid`),
FOREIGN KEY(`widgetid`) REFERENCES `widgets`(`widgetid`)
ON DELETE CASCADE
) ENGINE=INNODB;
The FOREIGN KEY instruction is pretty simple. First, you specify the column that is the foreign key, followed by what key it is referencing. In this case, we're referencing the widgetid column in the widgets table.
After we specify what column is related to a foreign column, we then determine what we should do in order to retain data integrity. In the example above, we have chosen to cascade deletions. This means when we run our DELETE statement from above, the changes will cascade down into any table with a foreign key referencing the deleted widgetid. Thus, the rows in widget_parts with the widgetid of 12 will be deleted when we remove the widget from the widgets table.
When specifying a Foreign Key, you have to specify not only what to do ON DELETE, but also what to do ON UPDATE. Failing to specify forces the default action of RESTRICT. With RESTRICT, the DELETE statement we ran above would not be permitted. In our CREATE statement above, we failed to specify an ON UPDATE action, and thus any statement we ran that attempted to modify the widgetid would be restricted and fail. If we chose to CASCADE changes ON UPDATE, then statements modifying the widgetid would cascade down and change the referencing column in widget_parts.
Keep your relationships strong with Foreign Keys and InnoDB!
