"Cannot add foreign key constraint" is MySQL telling you that the foreign key you defined cannot connect the two tables as written. The classic form is ERROR 1215 (HY000), or errno: 150 inside error 1005 when you create a table. Current versions are more specific and name the problem with a newer error code. The cause is almost always a mismatch between the child column and the parent key.
First, get the real reason
Older messages tell you nothing useful. InnoDB keeps the details of the most recent failure in its status output (FOREIGN KEY constraints):
SHOW ENGINE INNODB STATUS\G
-- look for the section:
-- ------------------------
-- LATEST FOREIGN KEY ERROR
-- ------------------------
On MySQL 8.0 and 8.4 you will often get one of these more specific errors right away (server error reference):
3780: referencing and referenced columns are incompatible;1822: missing index for the constraint in the referenced table;6125: missing unique key in the referenced table (MySQL 8.4, see below);1824: failed to open the referenced table, usually because it does not exist or is spelled differently;3734: missing column in the referenced table;1830: column cannot be NOT NULL, because the constraint usesSET NULL;1826: duplicate foreign key constraint name.
1. The column types do not match
-- parent
CREATE TABLE customers (id INT UNSIGNED NOT NULL PRIMARY KEY) ENGINE=InnoDB;
-- child: INT (signed) vs INT UNSIGNED
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE=InnoDB;
-- ERROR 3780 (HY000): Referencing column 'customer_id' and referenced
-- column 'id' in foreign key constraint 'orders_ibfk_1' are incompatible.
The documentation is strict here: the size and sign of integer and DECIMAL types must be the same (FOREIGN KEY constraints). INT and INT UNSIGNED do not match. Neither do INT and BIGINT. This bites after a migration widens the parent's primary key to BIGINT but leaves the child columns as INT, or when an ORM defaults to unsigned for IDs and a hand-written table does not.
Compare both sides directly instead of trusting your migration files:
SELECT table_name, column_name, column_type, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND (table_name, column_name) IN (('orders', 'customer_id'), ('customers', 'id'));
SELECT table_name, engine, create_options
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name IN ('orders', 'customers');
2. Character set or collation differs
For string columns, the length can differ, but the character set and collation must be the same. This is the most common cause on databases that were upgraded over the years. In MySQL 8.0 the default collation is utf8mb4_0900_ai_ci, while tables created on 5.7 often use utf8mb4_general_ci or the old utf8. A new table referencing an old one fails, even though both columns "look" like VARCHAR(36). The fix is to convert one side so the two match. On a big table, that conversion is a table rebuild in itself.
3. The parent key is not unique (MySQL 8.4)
-- MySQL 8.4: the referenced columns must be a PRIMARY KEY or UNIQUE key
ALTER TABLE orders ADD FOREIGN KEY (region_code) REFERENCES regions (code);
-- rejected: regions.code has only a non-unique index
-- (8.4 reports a missing unique key or a missing index for the constraint)
ALTER TABLE regions ADD UNIQUE KEY uq_regions_code (code); -- the real fix
InnoDB used to let a foreign key reference any indexed column, even a non-unique or partial index. That was a non-standard extension. Starting with MySQL 8.4.0 it is rejected by default: restrict_fk_on_non_standard_key is ON (What is new in MySQL 8.4). You can set it to OFF per session, but the variable is deprecated, and support for such keys is expected to be removed (restrict_fk_on_non_standard_key).
Existing foreign keys of this kind survive the upgrade from 8.0. The server writes warnings with their names. The trouble starts when you recreate or alter such a table on 8.4, for example in a migration or when restoring a dump. Fix it properly: add a UNIQUE key on the parent columns or point the foreign key at the primary key. If the parent values are not unique, the "foreign key" was never really one, and that is a data-model problem worth knowing about.
4. Missing or unusable index
MySQL needs an index on both sides, with the foreign key columns first and in the same order. On the child side it creates one automatically if needed. On the parent side it does not, and you get 1822. Prefix indexes don't count, so TEXT and BLOB columns cannot be part of a foreign key at all.
5. Wrong engine, partitioning or a generated column
- Both tables must use the same storage engine, and neither can be a temporary table. A legacy MyISAM parent table is a common cause.
- InnoDB does not support foreign keys on tables with user-defined partitioning, either parent or child.
- A foreign key cannot reference a virtual generated column.
All three are in the documented restrictions (FOREIGN KEY constraints). Check engine and create_options with the query above.
6. SET NULL on a NOT NULL column, and duplicate names
ON DELETE SET NULL on a column declared NOT NULL cannot work, and MySQL says so with error 1830. Either allow NULL or choose another action.
Constraint names must be unique in the whole database, not just in the table. A duplicate can also show up as errno 121 inside error 1005 (FOREIGN KEY constraints). This happens when a migration copies a table definition, including CONSTRAINT fk_customer, into a second table. Use names that include the table, for example fk_orders_customer.
Disabling foreign_key_checks is not a fix. It lets a dump load in any table order, which is what it is for. A column type mismatch is still rejected with checks off (FOREIGN KEY constraints). Rows written while checks are off are never verified later: turning checks back on does not scan existing data (foreign_key_checks).
When the definition is fine but the data is not
If the definition is correct, adding the key to a table with existing rows can still fail on orphaned data, with error 1452. Find the orphans first:
-- child rows that point to nothing: fix these before adding the FK
SELECT o.customer_id, COUNT(*)
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
GROUP BY o.customer_id
LIMIT 20;
Then decide per row: create the missing parent, set the reference to NULL, or delete the child. Our article on MySQL error 1452 covers the cleanup in detail.
On large tables and busy servers
On a test database, adding a foreign key takes a second. On a table with hundreds of millions of rows, the method matters more than the syntax:
- COPY versus INPLACE. With
foreign_key_checkson, adding a foreign key only supportsALGORITHM=COPY, which rebuilds the table. With checks off,INPLACEis supported (InnoDB online DDL). The fast path therefore relies on you having checked for orphans yourself first. In our experience, it does not validate existing rows for you. - Type fixes are rebuilds. Changing a column's data type, for example
INTtoBIGINTto match the parent, only works withALGORITHM=COPY. Plan that change separately from the constraint, with an online schema change tool. - Tool support. gh-ost does not support foreign keys (gh-ost limitations). pt-online-schema-change handles them but needs care with child tables. Read our guide on MySQL ALTER TABLE without downtime before choosing.
- Replication. A long ALTER runs again on every replica, which can lag behind by the duration of the ALTER. The 8.4 rule on non-standard keys also has a replication twist: a foreign key created on the primary with the restriction off succeeds on the replica regardless of the replica's setting (restrict_fk_on_non_standard_key).
- Write overhead. Every insert into the child and every delete or key update on the parent now has to check the other table. In our experience, this is noticeable on very write-heavy tables and on cascading deletes that touch millions of rows. Measure on production-like data.
- Upgrades to 8.4. Before you upgrade, search for foreign keys that point to non-unique keys. Otherwise the first migration that recreates such a table after the upgrade will fail in production.
-- after the orphan check came back empty:
SET SESSION foreign_key_checks = 0;
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id),
ALGORITHM=INPLACE; -- fails instead of silently copying
SET SESSION foreign_key_checks = 1;
Key takeaways
- Run SHOW ENGINE INNODB STATUS or read the newer error code: 3780, 1822, 6125, 1824, 1830, 1826.
- Integer size and sign must match exactly; string charset and collation must match.
- MySQL 8.4 requires a PRIMARY KEY or UNIQUE key on the referenced columns by default.
- Same engine on both sides, no user-defined partitioning, no prefix indexes, no SET NULL on NOT NULL.
- Check for orphaned rows before adding the key, especially if you use the INPLACE path with checks off.
- On big tables, plan type changes and constraints separately; gh-ost does not support foreign keys.