SQL Tutorial › Database Design › FOREIGN KEY

SQL FOREIGN KEY

IntermediateLesson 54 of 6912 min read6 queries run on MySQL 8.4

A foreign key links a column in one table (the child) to the primary key of another (the parent). The database then guarantees referential integrity: no order for a customer who does not exist, no employee in a department that was deleted. This lesson shows the errors a foreign key raises and the actions (CASCADE, SET NULL, RESTRICT) that decide what happens when a parent row changes.

In this lesson

Create a foreign key between parent and child tables
Understand errors 1452 (bad child) and 1451 (parent in use)
Choose ON DELETE / ON UPDATE actions
Add and drop foreign keys

Parent and child

In the sample database, orders.customer_id references customers.customer_id:

customers · 7 rows (some columns)

customer_id	customer_name
1	Ananya Sharma
2	Rohit Kumar
3	Fatima Khan
4	David Fernandes
5	Ishita Bose
6	Manoj Pillai
7	Sara Thomas

orders · 10 rows

order_id	customer_id	order_date	status
1001	1	2025-01-05	Delivered
1002	2	2025-01-17	Delivered
1003	1	2025-02-02	Delivered
1004	3	2025-02-14	Cancelled
1005	4	2025-03-08	Delivered
1006	5	2025-03-22	Shipped
1007	2	2025-04-10	Delivered
1008	1	2025-04-28	Pending
1009	3	2025-05-15	Delivered
1010	5	2025-06-01	Delivered

customers is the parent table; orders is the child.
Every orders.customer_id must exist in customers.

Create a foreign key

SQL

CREATE TABLE teams (  team_id   INT PRIMARY KEY,  team_name VARCHAR(30) NOT NULL);CREATE TABLE players (  player_id INT PRIMARY KEY,  name      VARCHAR(30) NOT NULL,  team_id   INT,  CONSTRAINT fk_player_team FOREIGN KEY (team_id) REFERENCES teams (team_id));INSERT INTO teams VALUES (1, 'Testers'), (2, 'Developers');INSERT INTO players VALUES (10, 'Asha', 1), (11, 'Ravi', 2), (12, 'Mona', NULL);SELECT p.name, t.team_name FROM players p LEFT JOIN teams t ON t.team_id = p.team_id;

ResultMySQL 8.4

mysql> CREATE TABLE teams (

Query OK, 0 rows affected

mysql> CREATE TABLE players (

Query OK, 0 rows affected

mysql> INSERT INTO teams VALUES (1, 'Testers'), (2, 'Developers')

Query OK, 2 rows affected

mysql> INSERT INTO players VALUES (10, 'Asha', 1), (11, 'Ravi', 2), (12, 'Mon…

Query OK, 3 rows affected

mysql> SELECT p.name, t.team_name FROM players p LEFT JOIN teams t ON t.team_…

name	team_name
Asha	Testers
Ravi	Developers
Mona	NULL

3 rows in set

How this query works, step by step

The parent table teams must exist first, with a primary (or unique) key.
FOREIGN KEY (team_id) REFERENCES teams (team_id) ties players.team_id to it.
Mona has NULL: a nullable foreign key means “not assigned”, which is allowed.
MySQL automatically creates an index on players.team_id for the foreign key.

Error 1452: child row without a parent

MySQL

INSERT INTO orders (order_id, customer_id, order_date, status) VALUES (2001, 99, '2026-01-10', 'Pending');

ResultMySQL 8.4

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))

Error 1451: parent row still in use

MySQL

DELETE FROM customers WHERE customer_id = 1;

ResultMySQL 8.4

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))

How this query works, step by step

Customer 99 does not exist, so the new order is rejected (1452).
Customer 1 has orders, so deleting the customer would orphan them (1451).
The default action is RESTRICT / NO ACTION: refuse the change.

ON DELETE CASCADE

With ON DELETE CASCADE, deleting a parent row deletes its child rows automatically:

MySQL

CREATE TABLE teams (team_id INT PRIMARY KEY, team_name VARCHAR(30) NOT NULL);CREATE TABLE players (  player_id INT PRIMARY KEY,  name      VARCHAR(30) NOT NULL,  team_id   INT,  FOREIGN KEY (team_id) REFERENCES teams (team_id) ON DELETE CASCADE);INSERT INTO teams VALUES (1, 'Testers'), (2, 'Developers');INSERT INTO players VALUES (10, 'Asha', 1), (11, 'Ravi', 2), (12, 'Karan', 2);DELETE FROM teams WHERE team_id = 2;SELECT * FROM players;

ResultMySQL 8.4

mysql> CREATE TABLE teams (team_id INT PRIMARY KEY, team_name VARCHAR(30) NOT…

Query OK, 0 rows affected

mysql> CREATE TABLE players (

Query OK, 0 rows affected

mysql> INSERT INTO teams VALUES (1, 'Testers'), (2, 'Developers')

Query OK, 2 rows affected

mysql> INSERT INTO players VALUES (10, 'Asha', 1), (11, 'Ravi', 2), (12, 'Kar…

Query OK, 3 rows affected

mysql> DELETE FROM teams WHERE team_id = 2

Query OK, 1 row affected

mysql> SELECT * FROM players

player_id	name	team_id
10	Asha	1

1 row in set

How this query works, step by step

Team 2 has two players.
DELETE FROM teams WHERE team_id = 2 removes the team and both of its players.
MySQL reports only 1 row affected (the team); the cascaded rows are not counted.

Watch out

CASCADE is powerful and silent. One DELETE on a parent can remove thousands of child rows. Use it for true “part of” relationships (an order’s lines) and not for important independent data.

ON DELETE SET NULL

MySQL

CREATE TABLE teams (team_id INT PRIMARY KEY, team_name VARCHAR(30) NOT NULL);CREATE TABLE players (  player_id INT PRIMARY KEY,  name      VARCHAR(30) NOT NULL,  team_id   INT,  FOREIGN KEY (team_id) REFERENCES teams (team_id) ON DELETE SET NULL);INSERT INTO teams VALUES (1, 'Testers'), (2, 'Developers');INSERT INTO players VALUES (10, 'Asha', 1), (11, 'Ravi', 2);DELETE FROM teams WHERE team_id = 2;SELECT * FROM players;

ResultMySQL 8.4

mysql> CREATE TABLE teams (team_id INT PRIMARY KEY, team_name VARCHAR(30) NOT…

Query OK, 0 rows affected

mysql> CREATE TABLE players (

Query OK, 0 rows affected

mysql> INSERT INTO teams VALUES (1, 'Testers'), (2, 'Developers')

Query OK, 2 rows affected

mysql> INSERT INTO players VALUES (10, 'Asha', 1), (11, 'Ravi', 2)

Query OK, 2 rows affected

mysql> DELETE FROM teams WHERE team_id = 2

Query OK, 1 row affected

mysql> SELECT * FROM players

player_id	name	team_id
10	Asha	1
11	Ravi	NULL

2 rows in set

Ravi stays, with team_id set to NULL: the player exists, the team does not.

The actions

Action	When the parent row is deleted (or its key updated)
`RESTRICT` / `NO ACTION` (default)	refuse the change while children exist
`CASCADE`	delete (or update) the child rows too
`SET NULL`	set the child’s foreign key column to NULL
`SET DEFAULT`	not supported by InnoDB

ON UPDATE CASCADE works the same way for changes to the parent’s key value.

Add and drop a foreign key

MySQL

CREATE TABLE reviews (review_id INT PRIMARY KEY, product_id INT, rating INT);ALTER TABLE reviews  ADD CONSTRAINT fk_review_product FOREIGN KEY (product_id) REFERENCES products (product_id);SELECT constraint_name, referenced_table_nameFROM information_schema.referential_constraintsWHERE constraint_schema = 'company' AND table_name = 'reviews';ALTER TABLE reviews DROP FOREIGN KEY fk_review_product;

ResultMySQL 8.4

mysql> CREATE TABLE reviews (review_id INT PRIMARY KEY, product_id INT, ratin…

Query OK, 0 rows affected

mysql> ALTER TABLE reviews

Query OK, 0 rows affected

mysql> SELECT constraint_name, referenced_table_name

CONSTRAINT_NAME	REFERENCED_TABLE_NAME
fk_review_product	products

1 row in set

mysql> ALTER TABLE reviews DROP FOREIGN KEY fk_review_product

Query OK, 0 rows affected

Rules for a valid foreign key

The parent column must be a primary key or have a unique index.
Both columns must have the same type (INT to INT, same sign and length for strings).
Both tables must use InnoDB (the default).
Existing child values must already exist in the parent when you add the key.

Quick quiz

1. Which table does the foreign key constraint live on?

2. What happens with ON DELETE CASCADE when a parent row is deleted?

3. Error 1452 means…

4. Can a foreign key column be NULL?

Foreign key FAQ

What is a foreign key in SQL?

A column whose values must match the primary or unique key of another table, so related rows always exist.

What is ON DELETE CASCADE?

An option on a foreign key: when a parent row is deleted, all child rows referencing it are deleted automatically.

How do I fix “Cannot add or update a child row: a foreign key constraint fails”?

Insert the parent row first, or correct the child value so it matches an existing parent key.

Do foreign keys slow down inserts?

Slightly, because each insert checks the parent index. The protection against orphaned data is almost always worth it.