A foreign key establishes a relationship between two tables in a MySQL database, ensuring data remains consistent. For example: an order must always belong to an existing customer. In this article, we explain what foreign keys are, how to create them, which storage engine you need, and how to fix common errors.
What is a foreign key?
A foreign key is a column in a table that refers to the primary key of another table. This establishes a relationship between tables, for example between customers and orders. MySQL checks with every insert, update, or delete whether the relationship remains valid; this is called referential integrity.
Foreign keys only work with the InnoDB storage engine. MyISAM does not support foreign keys, so always check that InnoDB is selected when creating a table.
Creating a foreign key with SQL
The most reliable way to add a foreign key is via SQL. Below is an example where the orders table refers to the customers table:
CREATE TABLE customers (