Incident Response

MySQL "Cannot add foreign key constraint": Causes and Fixes

Error 1215, errno 150, 3780, 1822, and in MySQL 8.4 a new one, 6125. They all mean the same thing: the two sides of the foreign key do not match. Here is how to find out exactly what does not match, and how to add the key on a large table without a long outage.

· 14 min read · AG Data Team

"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):

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

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:

-- 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

MySQL Errors InnoDB Schema
Work with us

Schema changes stuck on foreign keys?

We fix failing migrations, clean up orphaned data and add constraints to large production MySQL tables without long write locks.

Get in touch More from the blog