Working with Tables

Foreign Keys & Referential Integrity

A foreign key relationship allows you to declare that an index in one table is related to an index in another and allows you to place constraints on what may be done to the table containing the foreign key. The database enforces the rules of this relationship to maintain referential integrity.

For example, the commission table in the sampdb sample database contains an associate_id column, which we use to relate commission records to associates in the associate table. When we created these tables in earlier , we did not set up any explicit relationship between them.

Were we to do so, we would declare commission.associate_id to be a foreign key for the associate.associate_id column. That prevents a record from being entered into the commission table unless it contains an associate_id value that exists in the associate table.

(In other words, the foreign key prevents entry of commissions for non-existent associates.) We could also set up a constraint such that if an associate is deleted from the associate table, all corresponding records for the associate in the commission table should be deleted automatically as well.

This is called cascaded delete because the effect of the delete cascades from one table to another.

Foreign keys help maintain the consistency of your data, and they provide a certain measure of convenience. Without foreign keys, you are responsible for keeping track of inter-table dependencies and maintaining their consistency from within your applications. In many cases, doing this isn't really that much work.

It amounts to little more than adding a few extra DELETE statements to make sure that when you delete a record from one table, you also delete the corresponding records in any related tables.

But if your tables have particularly complex relationships, you may not want to be responsible for implementing these dependencies in your applications. Besides, if the database engine will perform consistency checks for you, why not let it?

Foreign key support in MySQL is provided by the InnoDB table handler. This section describes how to set up InnoDB tables to define foreign keys, and how foreign keys affect the way you use tables. But first, it's necessary to define some terms:

. The parent is the table that contains the original key values.

. The child is the related table that refers to key values in the parent.

. Parent table key values are used to associate the two tables. Specifically, the index in the child table refers to the index in the parent. Its values must match those in the parent or else be set to NULL to indicate that there is no associated parent table record.

The index in the child table is known as the foreign key—

that is, the key that is foreign (external) to the parent table but contains values that point to the parent. A foreign key relationship can be set up to disallow NULL values, in which case all foreign key values must match a value in the parent table.

InnoDB enforces these rules to guarantee that the foreign key relationship stays intact with no mismatches. This is called referential integrity.