Foreign keys are an integral part of creating a relationship in relational databases. Here's why, and how to create them.

So we've established that a primary key provides a unique identifier for the table. But primary keys aren't the only "key" type. Our database can also contain foreign keys.

What is a Foreign Key?

A foreign key is a column (or collection of columns) in one table that uniquely identifies a row of another table. This defines a relationship between the two tables.

A foreign key allows you to cross-reference related data across tables. This comes in handy when a column contains data that is represented in another table.

Example

Here's a diagram of our FruitShop database showing the relationship between the Fruit table and the Units table.

The black line that links the two tables indicates a foreign key. The UnitId field on the Fruit table is a foreign key to the UnitId field on the Units table. Therefore, the value that we insert into Fruit.UnitId must correspond to a value in Units.UnitId. This enables the Fruit.UnitId to reference the data in the other columns for that record (i.e. the record that has the corresponding UnitId).

The Data

So if our Fruit table contains a record like this:

FruitIdFruitNameInventoryUnitIdDateEnteredDateUpdated1Apple1032012-11-27 12:42:102012-11-27 12:42:10

And our Units table contains the following records:

UnitIdUnitNameDateEnteredDateUpdated1Piece2011-12-30 12:46:152011-12-30 12:46:152Bunch2011-12-30 12:46:152011-12-30 12:46:153Kilogram2011-12-30 12:46:152011-12-30 12:46:154Container2011-12-30 12:46:152011-12-30 12:46:155Pound2011-12-30 12:46:152011-12-30 12:46:156Ounce2011-12-30 12:46:152011-12-30 12:46:15

You can see that the Fruit.UnitId field contains a 3. Now look at the Units table for the record that contains a 3 in the UnitId field. You can see that this record represents Kilogram. Therefore, we now know that apples are measured in kilograms.

The good thing about setting up the database in this way is that, we don't need to repeat "Kilograms" for every record that uses that unit. Reducing duplication is a key benefit of relational database management systems.

Seeing as many records in the Fruit table will share the same unit name (eg, "Kilograms", "Container", "Bunch", etc), we should think carefully before adding duplicates to our database. Without using a foreign key relationship, we could just write the unit names straight into the Fruit table (and perhaps call the column "Unit", "UnitType" or "UnitName"). Then we would end up with many records sharing the same value for the unit name column. We would see "Kilogram" repeated over and over again against many records. We would also see "Bunch" repeated, and any other popular unit type.

While it is not necessarily "wrong" to do this, it is generally more efficient to store one record for each those unit names in a separate table, then reference that table via the UnitId column. Doing this is more efficient than repeating those unit names over and over again for every record that's created in the Fruits table. It also makes it easier if we ever decide to update a unit name (change "Kilograms" to "Kilos" for example). If we update a unit name, it wouldn't affect the Fruit table because the UnitId will remain the same. Plus it also helps prevent inconsistent data turning up in our database.

Foreign Key Constraint

A foreign key constraint is a database object that assists in keeping your foreign key data consistent. You create a foreign key constraint to maintain referential integrity. By creating a foreign key constraint, you are telling MySQL to enforce certain rules over the data. When data is inserted, deleted or updated, MySQL will check that it adheres to the foreign key that you created between tables. If not, it will prevent the data being written/overwritten/deleted, thus maintaining referential integrity.

For example, if a user tries to enter a UnitId value into the Fruit.UnitId column but there is no corresponding record in the Units.UnitId column, then MySQL will prevent the user from entering that value.

When we created our two tables, we added a foreign key constraint to the Fruit table. Here's the code we used to create the constraint:

CONSTRAINT fkFruitUnits FOREIGN KEY (UnitId) REFERENCES Units (UnitId) ON DELETE RESTRICT ON UPDATE CASCADE

When you expand the nodes in the left SCHEMAS tab, you can see the foreign key we created (as well as the primary keys):

If you try to insert data that doesn't conform to the foreign key constraint, you should get an error.

For example, if I attempt to insert a record into the Fruit table using a UnitId value that doesn't exist in the Units table, I receive the following error:

This occurs because I'm trying to insert a value of 5 into the UnitId column when there's no corresponding value in the Units.UnitId field.

For this to succeed, I would need to ensure there's a record in the Units table with a UnitId of 5.

Foreign Key Not Working?

You might encounter the occasional situation where a foreign key doesn't appear to work. For example, you can successfully insert data into a table even though there's a foreign key that should prevent that data from being inserted.

There's a few things you can check in this situation.

• Ensure you have appended ON DELETE and ON UPDATE clauses in your code. For example, ON DELETE RESTRICT ON UPDATE CASCADE. See our CREATE TABLE example for when to place this code.
• Ensure the table is InnoDB. You can do this by adding ENGINE=InnoDB to the end of your CREATE TABLE statement (see my example from when we created our tables). Some engines (such as MyISAM) don't support foreign key constraints, but they don't provide any warning about this when you attempt to create your foreign key constraint. If your default engine is not InnoDB then it's likely that your foreign keys won't be supported.
• Ensure MySQL is actually checking foreign keys. You can do this by running the following code: SET FOREIGN_KEY_CHECKS=1.

Disable Foreign Key Checking

There may be times when foreign key constraints can become unnecessarily restrictive — to the point where they severely hamper your efforts in loading data. For example, when you've just created a database and you need to load the initial data. Or if you need to drop a bunch of tables and reload the data.

If you don't load the data in the correct order, you'll probably keep getting foreign key errors due to the data being loaded in the wrong order (i.e. you're trying to load the child tables before the parent tables have had their data loaded).

This isn't just an issue for when loading the data. You could also encounter this issue when creating the database in the first place. If you don't create the tables in the correct order you could encounter errors due to any foreign key constraints.

If you don't know the correct parent-child order it could possibly take a lot of time and effort to establish the correct order to create the database or load the data. In cases like these, you might be better off telling MySQL temporarily not to check foreign keys for now.