MySQL Foreign Key

Last updated: October 19, 2026Oct 19, 2026 6 min read

A foreign key links a column in one table (the child) to the primary key of another table (the parent). MySQL then guarantees that every child row points to a parent that really exists. This is called referential integrity: no orders for missing customers, no marks for students who were never enrolled.

1. How a Foreign Key Works

Figure 1: every orders.customer_id must point to an existing customers.customer_id.

The child column and the parent column must have compatible types (same integer type and sign, same character set for strings).
The parent column must be indexed, normally its PRIMARY KEY or a UNIQUE key.
MySQL automatically adds an index on the child column if none exists.
Foreign keys work only with InnoDB tables.

2. Creating a Foreign Key

syntax.sql

[CONSTRAINT fk_name] FOREIGN KEY (child_column)
  REFERENCES parent_table (parent_column)
  [ON DELETE action] [ON UPDATE action]

create-fk.sql

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount      DECIMAL(9,2) NOT NULL,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
INSERT INTO orders VALUES (501, 1, 820), (502, 1, 1999), (503, 3, 450);
SELECT o.order_id, c.name, o.amount
FROM orders o JOIN customers c ON c.customer_id = o.customer_id;

Result: 3 rows

order_id	name	amount
501	Aditya	820.00
502	Aditya	1999.00
503	Charu	450.00

3. What It Prevents

An order for a customer that doesn't exist:

query.sql

INSERT INTO orders VALUES (504, 9, 300);

Output

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

Deleting a customer who still has orders:

query.sql

DELETE FROM customers WHERE customer_id = 1;

Output

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

Changing a customer's ID that orders refer to:

query.sql

UPDATE customers SET customer_id = 10 WHERE customer_id = 3;

Output

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

A customer with no orders can be deleted freely:

query.sql

DELETE FROM customers WHERE customer_id = 2;

Output

Query OK, 1 row affected

4. Referential Actions: ON DELETE and ON UPDATE

Action	When the parent row is deleted / its key updated…	Typical use
`RESTRICT` / `NO ACTION` (default)	Refuse if child rows exist	Orders, invoices: never lose history
`CASCADE`	Delete / update the child rows too	Order items belong to an order
`SET NULL`	Set the child column to NULL (it must allow NULL)	Optional links: a ticket's assigned agent leaves
`SET DEFAULT`	Parsed but rejected by InnoDB	Not supported

5. ON DELETE CASCADE

Order items make no sense without their order, so deleting an order should delete its items:

cascade.sql

CREATE TABLE order_items (
  order_id INT,
  line_no  INT,
  product  VARCHAR(30),
  qty      INT,
  PRIMARY KEY (order_id, line_no),
  CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders (order_id)
    ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO order_items VALUES (501,1,'Earphones',1),(502,1,'Smart Watch',1),(502,2,'Strap',2),(503,1,'Charger',1);

DELETE FROM orders WHERE order_id = 502;
SELECT * FROM order_items;

Result: 2 rows

order_id	line_no	product	qty
501	1	Earphones	1
503	1	Charger	1

Both items of order 502 were removed automatically. ON UPDATE CASCADE works the same way for key changes:

update-cascade.sql

UPDATE orders SET order_id = 5030 WHERE order_id = 503;
SELECT * FROM order_items WHERE line_no = 1;

Result: 2 rows

order_id	line_no	product	qty
501	1	Earphones	1
5030	1	Charger	1

CASCADE is powerful: one DELETE can remove rows in many tables through chains of cascades. Use it only where the child truly can't exist without the parent.

6. ON DELETE SET NULL

set-null.sql

CREATE TABLE agents (agent_id INT PRIMARY KEY, name VARCHAR(30));
CREATE TABLE tickets (
  ticket_id INT PRIMARY KEY,
  subject   VARCHAR(50),
  agent_id  INT NULL,
  CONSTRAINT fk_ticket_agent FOREIGN KEY (agent_id) REFERENCES agents (agent_id) ON DELETE SET NULL
);
INSERT INTO agents VALUES (1,'Esha'),(2,'Farid');
INSERT INTO tickets VALUES (900,'Refund not received',1),(901,'Wrong size',2),(902,'Late delivery',1);

DELETE FROM agents WHERE agent_id = 1;
SELECT * FROM tickets;

Result: 3 rows

ticket_id	subject	agent_id
900	Refund not received	NULL
901	Wrong size	2
902	Late delivery	NULL

Esha's tickets were kept and are now unassigned.

7. Adding and Dropping Foreign Keys

Add a foreign key to an existing table. Existing rows must already be valid:

add-fk.sql

CREATE TABLE reviews (review_id INT PRIMARY KEY, customer_id INT, stars TINYINT);
INSERT INTO reviews VALUES (1, 1, 5), (2, 7, 3);
ALTER TABLE reviews ADD CONSTRAINT fk_review_customer
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id);

Output

Query OK, 0 rows affected
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`shop`.`#sql-1527f_1ff`, CONSTRAINT `fk_review_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))

Find the orphan rows, fix them, then add the key:

find-orphans.sql

SELECT r.* FROM reviews r
LEFT JOIN customers c ON c.customer_id = r.customer_id
WHERE c.customer_id IS NULL;

Result: 1 row

review_id	customer_id	stars
2	7	3

add-fk-again.sql

DELETE FROM reviews WHERE review_id = 2;
ALTER TABLE reviews ADD CONSTRAINT fk_review_customer
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id);

Output

Query OK, 1 row affected
Query OK, 1 row affected
Records: 1  Duplicates: 0  Warnings: 0

Drop a foreign key by its constraint name (the automatically created index stays until you drop it too):

query.sql

ALTER TABLE reviews DROP FOREIGN KEY fk_review_customer;

Output

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

8. Self-Referencing Foreign Keys

A table can refer to itself, for example each employee's manager is another employee:

self-fk.sql

CREATE TABLE staff (
  staff_id   INT PRIMARY KEY,
  name       VARCHAR(30),
  manager_id INT NULL,
  CONSTRAINT fk_staff_manager FOREIGN KEY (manager_id) REFERENCES staff (staff_id)
);
INSERT INTO staff VALUES (1,'Gita',NULL),(2,'Hari',1),(3,'Isha',1),(4,'Jai',2);
SELECT e.name AS employee, m.name AS manager
FROM staff e LEFT JOIN staff m ON m.staff_id = e.manager_id;

Result: 4 rows

employee	manager
Gita	NULL
Hari	Gita
Isha	Gita
Jai	Hari

9. Listing Foreign Keys

query.sql

SELECT constraint_name, table_name, referenced_table_name, update_rule, delete_rule
FROM information_schema.referential_constraints
WHERE constraint_schema = 'shop'
ORDER BY table_name;

Result: 4 rows

CONSTRAINT_NAME	TABLE_NAME	REFERENCED_TABLE_NAME	UPDATE_RULE	DELETE_RULE
fk_items_order	order_items	orders	CASCADE	CASCADE
fk_orders_customer	orders	customers	NO ACTION	NO ACTION
fk_staff_manager	staff	staff	NO ACTION	NO ACTION
fk_ticket_agent	tickets	agents	NO ACTION	SET NULL

10. Disabling Checks for Bulk Loads

When restoring a dump, tables may be loaded in any order. SET foreign_key_checks = 0; turns checking off for your session, and = 1 turns it back on. MySQL does not re-check existing rows when you turn it back on, so only use this with data you trust (mysqldump files do this automatically).

11. Summary

A foreign key makes a child column refer to a parent key, so every reference is valid.
It blocks orphan inserts and, by default, deleting or changing referenced parents.
ON DELETE/UPDATE CASCADE follows the parent; SET NULL unlinks children; RESTRICT refuses.
Add with ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY; drop with DROP FOREIGN KEY name.
Foreign keys require InnoDB, matching types and an indexed parent column.

Was this tutorial helpful?

Help Us Improve

MySQL Foreign Key

1. How a Foreign Key Works

2. Creating a Foreign Key

3. What It Prevents

4. Referential Actions: ON DELETE and ON UPDATE

5. ON DELETE CASCADE

6. ON DELETE SET NULL

7. Adding and Dropping Foreign Keys

8. Self-Referencing Foreign Keys

9. Listing Foreign Keys

10. Disabling Checks for Bulk Loads

11. Summary