FOREIGN KEY

FOREIGN KEY

After your primary key is in place, you can turn your attention to foreign keys. Foreign key constraints typically are used to define relationships among two or more tables, usually in the context of "parent-child" or "header-detail" associations. In addition to enforcing data integrity, MySQL lets you use foreign keys to specify actions to take when rows are modified or deleted. This saves your developers from having to code those rules into their applications; it also improves performance because the rules can be run on the server immediately. These actions include the following:

• CASCADE

• RESTRICT

• SET NULL

• NO ACTION

• SET DEFAULT

Note that some versions of MySQL might not yet support all of the preceding actions. In addition, MySQL is always introducing new features, so be certain to check the documentation for your version to see if new foreign key actions have been implemented.

To use foreign keys, your database must satisfy the following criteria:

• Use the InnoDB storage engine.

• Have indexes in place on the appropriate columns.

• Match data types for the related columns. If the related columns are dissimilar, MySQL has no way to join them and enforce the constraint.

In the case of High-Hat Airways' new lost luggage application, suppose that you want to create a table (missing_luggage) to hold records of misplaced luggage to complement the checked_luggage table you created earlier in this chapter.

After consulting with your customers (the users of the application), you realize that these two tables have a parent-child relationship: No records can exist in missing_luggage without a corresponding parent in checked_luggage. Thus, your SQL to create the child table looks like this:

 CREATE TABLE missing_luggage(     missing_luggage_id INT AUTO_INCREMENT PRIMARY KEY,     luggage_check_id INT, ... ...     FOREIGN KEY (luggage_check_id) REFERENCES checked_luggage(luggage_check_id) ) ENGINE = INNODB; 

You can use the MySQL Table Editor to view this foreign key, as shown in Figure 5.2.

Figure 5.2. Using the MySQL Table Editor to view foreign key details.

As the constraint currently stands, MySQL returns an error if users attempt to enter a child record without a corresponding parent record in checked_luggage:

 ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails 

This helps improve performance and improve integrity at the same time. Unfortunately, this is not enough for one of the new hotshot MBAs brought in after the indictments of the CEO and CFO. She has a new idea for an application feature: What if High-Hat could simply purge its database of all missing luggage every so often? This would certainly improve the results of internal missing luggage reports, and could help her achieve her next promotion.

You could code this operation in SQL, but it's much easier to associate ON DELETE CASCADE with MySQL's foreign key constraints to take care of this task. Setting a cascading delete means that whenever a parent row (that is, a record in checked_luggage) is deleted, MySQL will employ the foreign key constraint to find all relevant child records (that is, related rows in missing_luggage), and then delete them.

 CREATE TABLE missing_luggage(     missing_luggage_id INT AUTO_INCREMENT PRIMARY KEY,     luggage_check_id INT, ... ...     FOREIGN KEY (luggage_check_id) REFERENCES checked_luggage(luggage_check_id) ON DELETE CASCADE ) ENGINE = INNODB; 

Several weeks later, the hotshot MBA is fired when her database manipulations are brought to light by an anonymous informant. Her replacement wants you to simply disassociate, rather than delete, child records from their parent when the parent is removed. This is much less likely to result in his termination and/or indictment.

In this case, you might use the ON DELETE NULL option as part of your foreign key declaration:

 CREATE TABLE missing_luggage(     missing_luggage_id INT AUTO_INCREMENT PRIMARY KEY,     luggage_check_id INT, ... ...     FOREIGN KEY (luggage_check_id) REFERENCES checked_luggage(luggage_check_id) ON DELETE SET NULL ) ENGINE = INNODB; 

This type of foreign key constraint sets the child's related column(s) to NULL when a parent row is removed. The child still remains, but it is now an orphan.

Let's spend a few moments discussing what happens when constraints such as UNIQUE, PRIMARY KEY, and FOREIGN KEY are violated. If you really want your operations to proceed despite these violations, you can use the IGNORE keyword when running your statement. You can also use the SHOW WARNINGS statement to learn what has happened. Note that constraint violation behavior might differ if you are using the InnoDB storage engine or the MyISAM engine. Optimal transaction planning is discussed in Chapter 9, "Developing High-Speed Applications."

Finally, be careful when setting up foreign key constraints: It is possible to create a circular reference. For example, table A might reference table B, which might, in turn, reference table C, which unwisely references table A. MySQL has defenses against the infinite loops that this might cause, but it's still a good idea to design these constraints cautiously.