Understanding MySQL Foreign Keys

Primary keys serve as unique identifiers for the records in a table, while foreign keys are used to link related tables together. When designing a set of database tables, it is important to specify which fields will be used for primary and foreign keys to clarify both in-table structure and inter-table relationships.

About Foreign Keys

The fundamental basis of a relational database system like MySQL is its capability to create relationships between the tables that make up the database. By making it possible to easily relate records in different tables to one another, an RDBMS makes it possible to analyze data in different ways while simultaneously keeping it organized in a systematic fashion, with minimal redundancy.

These relationships are managed through the use of foreign keys, essentially, fields that have the same meaning in all the tables in the relationship and that serve as points of commonality to link records in different tables together. A foreign key relationship could be one-to-one (a record in one table is linked to one and only one record in another table) or one-to-many (a record in one table is linked to multiple records in another table).

FIGURE 1 A one-to-one relationship between tables

NOTE

Foreign keys are only supported on InnoDB tables.

Figure 1 illustrates a one-to-one relationship: a service and its associated description, with the relationship between the two managed via the unique ServiceID field.

Figure 2 illustrates a one-to-many relationship: an author and his or her books, with the link between the two maintained via the unique AuthorID field.

FIGURE 2 A one-to-many relationship between tables

When creating a table, a foreign key can be defined in much the same way as a primary key by using the FOREIGN KEY…REFERENCES modifier. The following example demonstrates by creating two InnoDB tables linked to each other in a one-to-many relationship by the aircraft type identifier:

mysql> CREATE TABLE aircrafttype (
 -> AircraftTypeID smallint(4) unsigned NOT NULL AUTO_INCREMENT,
 -> AircraftName varchar(255) NOT NULL,
 -> PRIMARY KEY (AircraftTypeID)
 -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.61 sec)
mysql> CREATE TABLE aircraft (
 -> AircraftID smallint(4) unsigned NOT NULL AUTO_INCREMENT,
 -> AircraftTypeID smallint(4) unsigned NOT NULL,
 -> RegNum char(6) NOT NULL,
 -> LastMaintEnd date NOT NULL,
 -> NextMaintBegin date NOT NULL,
 -> NextMaintEnd date NOT NULL,
 -> PRIMARY KEY (AircraftID),
 -> UNIQUE RegNum (RegNum),
 -> INDEX (AircraftTypeID),
 -> FOREIGN KEY (AircraftTypeID)
 -> REFERENCES aircrafttype (AircraftTypeID)
 -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.45 sec)

 

In this example, the aircraft.AircraftTypeID field is a foreign key linked to the aircrafttype.AircraftTypeID primary key. Note the manner in which this relationship is specified in the FOREIGN KEY…REFERENCES modifier. The FOREIGN KEY part specifies one end of the relationship (the field name in the current table), while the REFERENCES part specifies the other end of the relationship (the field name in the referenced table).

TIP

As a general rule, it’s a good idea to use integer fields as foreign keys rather than character fields, as this produces better performance when joining tables.

Once a foreign key is set up, MySQL only allows entry of those values into the aircraft types into the aircraft table that also exist in the aircrafttype table. Continuing the previous example, let’s see how this works.

mysql> INSERT INTO aircrafttype
 -> (AircraftTypeID, AircraftName)
 -> VALUES (503, 'Boeing 747');
Query OK, 1 row affected (0.09 sec)
mysql> INSERT INTO aircraft
 -> (AircraftID, AircraftTypeID, RegNum,
 -> LastMaintEnd, NextMaintBegin, NextMaintEnd)
 -> VALUES
 -> (3451, 503, 'ZX6488',
 -> '2007-10-01', '2008-10-23', '2008-10-31');
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO aircraft
 -> (AircraftID, AircraftTypeID, RegNum,
 -> LastMaintEnd, NextMaintBegin, NextMaintEnd)
 -> VALUES
 -> (3452, 616, 'ZX6488',
 -> '2007-10-01', '2008-10-23', '2008-10-31');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`db1`.`aircraft`, CONSTRAINT `aircraft_ibfk_1` FOREIGN KEY
(`AircraftTypeID`) REFERENCES `aircrafttype` (`AircraftTypeID`))

Thus, because an aircraft type with identifier 616 doesn’t exist in the aircrafttype, MySQL rejects the record with that value for the aircraft table. In this manner, foreign key constraints can significantly help in enforcing the data integrity of the tables in a database and reducing the occurrences of “bad” or inconsistent field values.

The following three constraints must be kept in mind when linking tables with foreign keys:

• All the tables in the relationship must be InnoDB tables. In non-InnoDB tables, the FOREIGN KEY…REFERENCES modifier is simply ignored by MySQL.
• The fields used in the foreign key relationship must be indexed in all referenced tables (InnoDB will automatically create these indexes for you if you don’t specify any).
• The data types of the fields named in the foreign key relationship should be similar. This is especially true of integer types, which must match in both size and sign.

What’s interesting to note is this: Even if foreign key constraints exist on a table, MySQL permits you to DROP the table without raising an error (even if doing so would break the foreign key relationships established earlier). In fact, in versions of MySQL earlier than 4.0.13, dropping the table was the only way to remove a foreign key. MySQL 4.0.13 and later does, however, support a less drastic way of removing a foreign key from a table, via the ALTER TABLE command. Here’s an example:

mysql> ALTER TABLE aircraft DROP FOREIGN KEY aircraft_ibfk_1;
Query OK, 1 row affected (0.57 sec)
Records: 1 Duplicates: 0 Warnings: 0

To remove a foreign key reference, use the DROP FOREIGN KEY clause with the internal name of the foreign key constraint. This internal name can be obtained using the SHOW CREATE TABLE statement. And in case you’re wondering why you must use the internal constraint name and not the field name in the DROP FOREIGN KEY clause … well, that’s a good question!

Automatic Key Updates and Deletions

Foreign keys can certainly take care of ensuring the integrity of newly inserted records. But what if a record is deleted from the table named in the REFERENCES clause? What happens to all the records in subordinate tables that use this value as a foreign key?

Obviously, those records should be deleted as well, or else you’ll have orphan records cluttering your database. MySQL 3.23.50 and later simplifies this task by enabling you to add an ON DELETE clause to the FOREIGN KEY…REFERENCES modifier, which tells the database what to do with the orphaned records in such a situation. Here’s a sequence that demonstrates this:

mysql> CREATE TABLE aircraft (
 -> AircraftID smallint(4) unsigned NOT NULL AUTO_INCREMENT,
 -> AircraftTypeID smallint(4) unsigned NOT NULL,
 -> RegNum char(6) NOT NULL,
 -> LastMaintEnd date NOT NULL,
 -> NextMaintBegin date NOT NULL,
 -> NextMaintEnd date NOT NULL,
 -> PRIMARY KEY (AircraftID),
 -> UNIQUE RegNum (RegNum),
 -> FOREIGN KEY (AircraftTypeID)
 -> REFERENCES aircrafttype (AircraftTypeID)
 -> ON DELETE CASCADE
 -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.17 sec)
mysql> INSERT INTO aircraft
 -> (AircraftID, AircraftTypeID, RegNum,
 -> LastMaintEnd, NextMaintBegin, NextMaintEnd)
 -> VALUES
 -> (3451, 503, 'ZX6488',
 -> '2007-10-01', '2008-10-23', '2008-10-31');
Query OK, 1 row affected (0.05 sec)
mysql> DELETE FROM aircrafttype;
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM aircraft;
Empty set (0.01 sec)

 

MySQL 4.0.8 and later also lets you perform these automatic actions on updates by allowing the use of an ON UPDATE clause, which works in a similar manner to the ON DELETE clause. So, for example, adding the ON UPDATE CASCADE clause to a foreign key definition tells MySQL that when a record is updated in the primary table (the table referenced for foreign key checks), all records using that foreign key value in the current table should also be automatically updated with the new values to ensure the consistency of the system.

Table 1 lists the four keywords that can follow an ON DELETE or ON UPDATE clause.

TABLE 1 Actions Available in ON DELETE and ON UPDATE Clause

CAUTION

Be aware that setting up MySQL for automatic operations through ON UPDATE and ON DELETE rules can result in serious data corruption if your key relationships aren’t set up perfectly. For example, if you have a series of tables linked together by foreign key relationships and ON DELETE CASCADE rules, a change in any of the master tables can result in records, even records linked only peripherally to the original deletion, getting wiped out with no warning. For this reason, you should check (and then double-check) these rules before finalizing them.