MySQL Foreign Keys: How and why with examples

March 5, 2025

11 min read

Foreign keys are a tool to make your SQL table data accurate and safe. In this tutorial, learn what a foreign key is, how to use them, and what they're good for.

What's a foreign key?

A foreign key is a way to declare that values in one table must also be present in another table. The database will enforce this constraint for you, preventing operations that would cause it to be violated.

Let's look at a simple example. We'll call our two tables parent and child.

CREATE TABLE parent (
    id bigint PRIMARY KEY,
    name varchar(100)
);

CREATE TABLE child (
    id bigint PRIMARY KEY,
    parent_id bigint,
    name varchar(100)
);

As the naming suggests, every row in child refers to a row in parent with its parent_id column. We can create a foreign key constraint that enforces this relationship with an ALTER TABLE statment on the child table. We'll comment every line with what it means.

ALTER TABLE 
    child -- the table with the constraint
    ADD CONSTRAINT -- clause to add a constraint
    fk_child_parent -- name of the constraint, optional
    FOREIGN KEY (parent_id) -- the columns in the child table
    REFERENCES parent(id); -- the columns in the parent table

Now when I try to insert a value into the child table that isn't in the parent, MySQL prevents me.

INSERT INTO parent VALUES (1, 'Tim'), (2, 'Zach');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

INSERT INTO child values (1, 1, 'Jack');
Query OK, 1 row affected (0.01 sec)

INSERT INTO child values (2, 3, 'Kai');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_child_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

The insert fails because there's no parent row with an id of 3. If we add one, the insert succeeds.

INSERT INTO parent VALUES (3, 'Aaron');
Query OK, 1 row affected (0.01 sec)

INSERT INTO child values (2, 3, 'Kai');
Query OK, 1 row affected (0.01 sec)

So you can't put a parent_id in the child table unless it's in the parent table already. Similarly, you can't delete rows in the parent table if those rows are referenced by rows in the child table.

DELETE FROM parent WHERE name = 'Tim';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_child_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

You also can't UPDATE the value of the referenced column, if that change would violate the constraint.

UPDATE PARENT SET id = 100 WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_child_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

Other referential actions

In the examples above, we're using the default referential action for foreign key constraints in MySQL, which is called RESTRICT. This means that the database will prevent any operations on the parent table that would cause the constraint to be violated.

Because it's the default, we didn't have to specify it when creating the constraint. But we could have done so like this:

ALTER TABLE child ADD CONSTRAINT fk_child_parent 
    FOREIGN KEY (parent_id) REFERENCES parent(id)
    ON DELETE RESTRICT ON UPDATE RESTRICT;

Note that you can specify an enforcement action for UPDATE and DELETE operations separately, and they can be different. There are two other actions that you might find useful in some schemas.

Cascading UPDATE and DELETE

The most common referential action besides the default is CASCADE. This action causes changes in the parent table to "cascade" down into child tables:

Updating the value of a column causes child table values to be updated as well
Deleting a row in the parent table causes any child table rows to be deleted

Let's look at an example using the same parent and child tables as above.

ALTER TABLE child 
    ADD CONSTRAINT fk_child_parent
    FOREIGN KEY (parent_id) REFERENCES parent(id)
    ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

UPDATE PARENT SET id = 100 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM child;
+----+-----------+------+
| id | parent_id | name |
+----+-----------+------+
|  1 |       100 | Jack |
|  2 |         3 | Kai  |
+----+-----------+------+
2 rows in set (0.00 sec)

DELETE FROM parent where id = 3;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM child;
+----+-----------+------+
| id | parent_id | name |
+----+-----------+------+
|  1 |       100 | Jack |
+----+-----------+------+
1 row in set (0.01 sec)

As you can see, when we updated or delete values in the parent table, those changes also affect the child table.

SET NULL

A less commonly used referential action is SET NULL. We can create a constraint with this action like so:

ALTER TABLE child ADD CONSTRAINT fk_child_parent 
    FOREIGN KEY (parent_id) REFERENCES parent(id)
    ON DELETE SET NULL ON UPDATE SET NULL;

Now changes to the referenced columns in the parent table will cause any impacted child rows to receive a NULL value for their parent_id.

ALTER TABLE child 
    ADD CONSTRAINT fk_child_parent
    FOREIGN KEY (parent_id) REFERENCES parent(id)
    ON DELETE SET NULL ON UPDATE SET NULL;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

UPDATE PARENT SET id = 100 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT * FROM child;
+----+-----------+------+
| id | parent_id | name |
+----+-----------+------+
|  1 |      NULL | Jack |
|  2 |         3 | Kai  |
+----+-----------+------+
2 rows in set (0.00 sec)

DELETE FROM parent where id = 3;
Query OK, 1 row affected (0.00 sec)

SELECT * FROM child;
+----+-----------+------+
| id | parent_id | name |
+----+-----------+------+
|  1 |      NULL | Jack |
|  2 |      NULL | Kai  |
+----+-----------+------+
2 rows in set (0.00 sec)

Now instead of the child rows being updated to match the parent or deleted when the parent is, they are simply orphaned. No parent at all anymore.

This behavior makes sense for certain kinds of relationships, namely when the link between the tables is optional. If you choose this option, make sure that the column in the child table is not defined as NOT NULL.

NULL values in foreign keys

New MySQL usrs can be surprised by the behavior of NULL values in a foreign key. If the columns of the child table are declared nullable, it's always permissible for them to contain the value NULL, even if the parent database doesn't have a corresponding NULL entry.

INSERT INTO child values (3, NULL, 'Oliver');
Query OK, 1 row affected (0.01 sec)

Poor Oliver has no parent, and this doesn't violate the foreign key constraint.

If you don't want this behavior, declare the column NOT NULL to prevent NULL values from being inserted.

Specifying a foreign key when creating a table

The examples above create tables without constraints and then alter them to add a constraint. But if you know your entire schema ahead of time, it's more common to include the FOREIGN KEY constraint in the CREATE TABLE statement.

CREATE TABLE child (
  id bigint NOT NULL,
  parent_id bigint DEFAULT NULL,
  name varchar(100) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY fk_child_parent (parent_id),
  CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent (id)
);

Foreign keys on more than one column

The examples above use a single column for the foreign key relationship between the two tables, and this is the most common use case. But you sometimes have a good reason to declare a foreign key on more than one column.

Let's say that we aren't interested in tracking exact parent / child relationships, for privacy reasons. Instead, we're going to record the city for every parent and child, and enforce that no child lives in a city without at least one recorded parent. That lets us run analytics on the number of parents and children in each city, and helps ensure the data is accurate.

We'll define our tables like this:

CREATE TABLE parent (
    id bigint PRIMARY KEY,
    name varchar(100),
    city varchar(255),
    state varchar(2),
    KEY (name, city)
);

CREATE TABLE child (
    id bigint PRIMARY KEY,
    name varchar(100),
    city varchar(255),
    state varchar(2),
    CONSTRAINT fk_child_state FOREIGN KEY (city, state) REFERENCES parent (city, state)
);

Now we can be sure that for each child, there's at least one parent in that same city, but we don't know exactly who.

Parent table requirements: use an index

Our last article in this series discussed secondary indexes in MySQL, and we mentioned that foreign keys require an index on both tables. This is because enforcing the constraint would be too expensive without an index -- every time you inserted a value in the child table, you would need to scan the entire parent table to make sure the constraint was valid. This would make INSERT operations too slow, so the database won't let you do it.

In the example above, our parent table has an index on the columns name, city. What happens if we remove it?

CREATE TABLE parent (
    id bigint PRIMARY KEY,
    name varchar(100),
    city varchar(255),
    state varchar(2),
    KEY (name, city)
);

CREATE TABLE child (
    id bigint PRIMARY KEY,
    name varchar(100),
    city varchar(255),
    state varchar(2),
    CONSTRAINT fk_child_state FOREIGN KEY (city, state) REFERENCES parent (city, state)
);

Failed to add the foreign key constraint. Missing index for constraint 'fk_child_state' in the referenced table 'parent'

So we need to create that index before we can create the foreign key. But oddly, MySQL doesn't require you to create the corresponding index on the child table -- it will create one automatically as needed. After creating the table, if we run SHOW CREATE TABLE on it, we can see that it has an index on those two columns.

CREATE TABLE `child2` (
  `id` bigint NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `state` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_child_state2` (`city`,`state`),
  CONSTRAINT `fk_child_state2` FOREIGN KEY (`city`, `state`) REFERENCES `parent2` (`city`, `state`)

This doesn't come up that often, because by far the most common use case for foreign keys is referring to another table by its primary key, which is already indexed.

Examining the foreign keys on a table

The easiest way to examine the foreign keys on a table is with the SHOW CREATE TABLE statement just like we did above. The output includes the entire statement required to recreate the schema of the table, including all constraints, indexes, and other properties of the table.

SHOW CREATE TABLE child;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table



                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| child | CREATE TABLE `child` (
  `id` bigint NOT NULL,
  `parent_id` bigint DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_child_parent` (`parent_id`),
  CONSTRAINT `fk_child_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Dropping a foreign key

If you no longer want a foreign key constraint, you can drop it. First find its name by running SHOW CREATE TABLE on the child table, which will include the constraint name.

Then drop the constraint with an ALTER TABLE statement.

ALTER TABLE child DROP CONSTRAINT fk_child_parent;

Temporarily turning off foreign key checks

Sometimes it might be necessary or desirable to disable foreign keys for a little while. This can happen for a couple reasons:

You're doing a large data load and want it to be as fast as possible (foreign key checks make insert operations slower, see below)
You need to insert or update some data in a way that temporarily breaks the constraint, but you know it won't hurt anything.

For these times, MySQL offers an escape hatch with the foreign_key_checks system variable. Set it to a false value and MySQL will not attempt to enforce or even check any foreign key constraints for this session, or until you turn it back on.

SET foreign_key_checks = off;

What are foreign keys good for?

Now that we understand the basics of foreign keys in MySQL, let's talk about why you would want to use one.

Ensuring data correctness

A foreign key's primary job is to provide a guarantee of referential integrity. This means that if a column in one table refers to a row in another table, the database guarantees the reference is valid.

In our simple parent and child example above, the database preserves referential integrity by preventing any child rows that refer to a parent row that doesn't exist.

INSERT INTO child values (2, 3, 'Kai');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_child_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

Referential integrity means you can reason about the results of your queries more easily. Specifically, it guarantees that a JOIN between these two tables on the foreign key columns will return a row for every row in the child table.

Automating cleanups

Setting a foreign key's referential action to ON DELETE CASCADE can be a great way to clean out associated entities spread across multiple tables. Let's look at a hypothetical customer and order tracking system with these tables:

CREATE TABLE customers(
    id bigint PRIMARY KEY AUTO_INCREMENT,
    name varchar(255)
);

CREATE TABLE orders(
    id PRIMARY KEY AUTO_INCREMENT,
    customer_id bigint NOT NULL,
    order_date date,
    CONSTRAINT order_customer FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE
);

CREATE TABLE invoices(
    id PRIMARY KEY AUTO_INCREMENT,
    order_id bigint NOT NULL,
    invoice_data text,
    CONSTRAINT invoice_order FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE
);

With this schema, if I want to delete all records of a particular customer, I can issue a single DELETE statement:

DELETE FROM customers WHERE id = 100;

This will delete the given customer row, as well as any rows in the orders table with that ID. And those deleted orders rows will in turn cause the deletion of any matching invoices rows. You can chain any number of refernces together in this way, causing a single DELETE statement to cascade into many tables.

This approach has drawbacks as well, of course. But for some use cases, it's exactly what you want.

Make JOIN operations self-documenting and performant

Foreign keys don't by themselves make your JOIN operations any faster. What they do instead is make queries that use joins easier to write and more likely to perform well. The reasons for this:

A foreign key documents a relationship between two tables, so anyone reading the schema will see which columns should be used in the ON clause for the two tables when writing a JOIN.
Because foreign keys require indexes on the columns in the constraint, there will always be a way for the query engine to efficiently join the two tables, making your queries faster.

The bottom line: if two tables in your database refer to each other, it probably means you'll want to join them together at some point, which means you'll want to create the indexes that foreign keys give you automatically.

Downsides to foreign keys

Foreign keys are a popular idea in database schema design for all the reasons above. But it's important to consider their costs as well as their benefits.

Foreign keys require indexes to use. Creating an index is a blocking operation. On a large table it can take minutes or hours, and during that time you can't write to the table affected.
Indexes take space, similar to the tables themselves. Each additional index you create increases the amount of storage your database takes to store the same amount of rows.
Foreign keys make INSERT, DELETE, and UPDATE operations slower. For an INSERT into a child table, the database must check that a corresponding row in the parent table exists. For an UPDATE or DELETE operation, the database must check for matching rows in every child table and take the correct referential action. For tables with many foreign keys from other tables, this can make these operations much more expensive.

These downsides shouldn't prevent you from using foreign keys, but keep them in mind when designing your schema.

Conclusion

This tutorial should get you on your way using foreign keys in MySQL. They are essential to writing a production database schema that is easy to use and performs well.

Before you go, did we mention that we built a MySQL-compatible database with built-in version control called Dolt? Dolt is a great way to experiment with databases, because you have the safety of version control to fall back on -- if you mess up, you can always dolt reset --hard to roll back to a previous revision. Dolt also lets you diff two revisions of your database, so you can see what you changed whenever you run a command.

Dolt is free and open source, so go check it out! All of the examples in this tutorial work in Dolt's built-in SQL shell, so you can use it to follow along at home.

Have questions about Dolt or MySQL foreign keys? Join us on Discord to talk to our engineering team and meet other Dolt users.

Blog