How to Add a Foreign Key in MySQL
Last updated August 26, 2026 · By the SaturnSQL team
Use ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ... REFERENCES, and choose an ON DELETE action. The referenced column must be indexed (a primary key is fine) and both column types must match exactly, including UNSIGNED.
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers (id)
ON DELETE RESTRICT;ON DELETE options
RESTRICT (the default) blocks deleting a customer that still has orders. CASCADE deletes the orders along with the customer. SET NULL clears customer_id, which then must be nullable.
Error 3780 or errno 150 on ALTER almost always means a type mismatch (e.g. INT vs BIGINT UNSIGNED) or a missing index on the referenced column.
MyISAM accepts the syntax and ignores it
A MyISAM table parses FOREIGN KEY without complaint and then enforces nothing, which is worse than an error because it looks like it worked. Anything created recently is InnoDB, but imported dumps and old schemas are worth checking.
SELECT table_name, engine FROM information_schema.tables
WHERE table_schema = DATABASE() AND engine <> 'InnoDB';Common errors
ERROR 3780 (HY000): Referencing column 'customer_id' and referenced column 'id' in foreign key constraint 'orders_customer_fk' are incompatible.
The two columns are not the same type. Signedness counts: BIGINT and BIGINT UNSIGNED are incompatible, which bites constantly because AUTO_INCREMENT primary keys are often unsigned while the referencing column is not. For string keys the character set and collation have to match too.
Compare both definitions, change the child column to match the parent exactly, then add the constraint.
SELECT table_name, column_name, column_type
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND ((table_name = 'orders' AND column_name = 'customer_id')
OR (table_name = 'customers' AND column_name = 'id'));
ALTER TABLE orders MODIFY customer_id BIGINT UNSIGNED;ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`shop`.`#sql-1_9`, CONSTRAINT `orders_customer_fk` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))
The types line up, but existing rows point at parents that are not there. The odd `#sql-1_9` table name in the message is the temporary copy MySQL was building, not a table of yours.
List the orphans, then delete them or point them at a real parent before retrying.
SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id IS NOT NULL AND c.id IS NULL;ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'child_fk' in the referenced table 'plain'
The referenced column has no index. InnoDB needs one on the parent side to enforce the constraint, and a primary or unique key is the usual way to get it.
Index the parent column first.
ALTER TABLE plain ADD UNIQUE KEY plain_code_key (code);Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free