MySQL Foreign Keys

Posted: 23rd July 2009 by Tim in MySQL
Tags: , , , ,

Foreign keys are a good way to ensure consistency between tables, by ensuring that the value in one table column exists in another table. For example, you might have a person table which contains person_id, first_name and surname. You may then have another table, person_ages, which contains person_id and date_of_birth. You would add a foreign key to person_ages to ensure that all person_id values match values in person.

Foreign key constraints may be added in MySQL in two ways – either when creating the table or after it has been created. To add the foreign key when creating the table, add the syntax to the end of the definition like so:

CREATE TABLE `person_ages` (
`person_id` INT(11) UNSIGNED NOT NULL,
`date_of_birth` DATE NOT NULL,
FOREIGN KEY (`person_id`) REFERENCES `person`(`person_id`)
);

Note that the first person_id refers to the column in the person_ages table, and person(person_id) means that we’re linking that column to the person_id column in the person table.

If you’ve already created the table, you can add the foreign key like so:

ALTER TABLE `person` ADD FOREIGN KEY (`person_id`) REFERENCES `person`(`person_id`);

It’s worth remembering that MyISAM tables will not enforce foreign keys. That is, if you add a foreign key to a MyISAM table, it will exist but invalid values will be allowed. Therefore, you should use the InnoDB table type for your tables (There are situations where other table types are appropriate, but for most small databases InnoDB will work fine).

The field type for the table with the constraint and the table being referenced must be of the same type. For example, an UNSIGNED INT(10) field cannot reference an UNSIGNED INT(11) field. Foreign keys also cannot reference tables in other schemas, nor can they reference views.