(opens in a new tab)

MySQL Error 1215: Cannot Add Foreign Key

September 29, 2026 by Chat2DB Team

ERROR 1215 (HY000): Cannot add foreign key constraint is the message most MySQL users remember from 5.x, and it is famous for saying nothing about what is actually wrong. MySQL 8 improved this a lot: most of the situations that used to produce 1215 now raise a more specific error that names the column, the constraint and the problem. The catch is that tutorials, Stack Overflow answers and ORMs still talk about 1215, so it helps to know which modern error replaced it in each case.

This guide lists the errors you can hit when creating a foreign key, explains every common cause with a reproducible example, shows how to read the LATEST FOREIGN KEY ERROR section of SHOW ENGINE INNODB STATUS, and ends with a step-by-step fix script.

Every error message below was captured from real statements on MySQL 8.4.11 and MySQL 8.0.46 running in Docker. Where the two versions differ, both are shown.

The Error Codes You Can Get

If your schema already has foreign keys and the statement fails because of the data rather than the definition, you get a different error: ERROR 1452 (23000): Cannot add or update a child row. That one is covered in detail in MySQL error 1452: a foreign key constraint fails. This article focuses on definition errors.

Set Up a Test Schema

To reproduce everything below, start a throwaway MySQL server:

docker run -d --name fk-lab -e MYSQL_ROOT_PASSWORD=pw mysql:8.4
docker exec -it fk-lab mysql -uroot -ppw

Then create a parent table. Note that id is INT UNSIGNED and that one column uses a different character set, because both details matter later:

CREATE DATABASE shop;
USE shop;
 
CREATE TABLE customers (
  id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email  VARCHAR(100) NOT NULL,
  code   CHAR(8) CHARACTER SET latin1,
  region VARCHAR(10),
  KEY (region)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Cause 1: Data Type or Sign Mismatch

The referencing column and the referenced column must have the same type. For integers, that includes the size (INT vs BIGINT) and the sign (SIGNED vs UNSIGNED). This is by far the most common cause, usually because the parent key is INT UNSIGNED AUTO_INCREMENT and the child column was declared as plain INT:

CREATE TABLE orders1 (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

ERROR 3780 (HY000): Referencing column 'customer_id' and referenced column 'id' in foreign key constraint 'orders1_ibfk_1' are incompatible.

A wider type fails the same way. BIGINT UNSIGNED referencing INT UNSIGNED returns the identical 3780 message, and so does a DATETIME column referencing a DATE column.

What is allowed to differ:

• The length of VARCHAR columns. A VARCHAR(50) child column referencing a VARCHAR(100) parent key was accepted on both 8.0 and 8.4 in our tests, as the MySQL manual also states. The character set and collation must still match.
• Nullability. The child column may be NULL even when the parent key is NOT NULL. A NULL in the child simply means "no parent".
• Display width such as INT(11), which is deprecated and ignored for this check.

The fix is to change the child column to exactly the parent's type:

ALTER TABLE orders1 MODIFY customer_id INT UNSIGNED NOT NULL;

The same error also appears in the other direction. If a foreign key already exists and you try to widen the parent key alone, MySQL refuses:

ALTER TABLE customers MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;

ERROR 3780 (HY000): Referencing column 'customer_id' and referenced column 'id' in foreign key constraint 'fk_ok' are incompatible.

To change the type of a key that is already referenced, drop the foreign keys, alter the parent and all child columns, and re-create the foreign keys. Do it in a maintenance window, because each ALTER rebuilds the table.

Cause 2: Character Set or Collation Mismatch

For string keys, the character set and the collation must be the same on both sides. In the test schema, customers.code is latin1, so a utf8mb4 child column is rejected:

CREATE TABLE orders4 (
  id INT PRIMARY KEY,
  code CHAR(8) CHARACTER SET utf8mb4,
  FOREIGN KEY (code) REFERENCES customers(code)
);

ERROR 3780 (HY000): Referencing column 'code' and referenced column 'code' in foreign key constraint 'orders4_ibfk_1' are incompatible.

A collation difference alone is enough. utf8mb4_bin referencing utf8mb4_0900_ai_ci produced the same 3780 error in our tests.

This often happens after an upgrade or a partial migration: old tables were created with utf8 / utf8mb3 or utf8mb4_general_ci, and new tables pick up the MySQL 8 default utf8mb4_0900_ai_ci. Compare the two columns directly:

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'shop'
  AND (TABLE_NAME, COLUMN_NAME) IN (('customers','code'), ('orders4','code'));

Then align the child column with the parent (or convert both tables to the same collation):

ALTER TABLE orders4 MODIFY code CHAR(8) CHARACTER SET latin1;
-- or, for a whole table:
ALTER TABLE orders4 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Cause 3: No Unique Index on the Referenced Column

InnoDB needs an index on the referenced columns so that it can check the parent row quickly. What counts as a usable index changed between versions, and so did the error code.

Referencing a column with no index at all:

CREATE TABLE orders3 (
  id INT PRIMARY KEY,
  email VARCHAR(100),
  FOREIGN KEY (email) REFERENCES customers(email)
);

On MySQL 8.0.46:

ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'orders3_ibfk_1' in the referenced table 'customers'

On MySQL 8.4.11:

ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint 'orders3_ibfk_1' in the referenced table 'customers'

MySQL 8.4 Requires a Unique Key

MySQL 8.0 accepted a foreign key that pointed at a non-unique index, or at the leftmost column of a composite primary key. MySQL 8.4 rejects both by default. With the test schema, customers.region has a plain (non-unique) index:

CREATE TABLE orders7 (
  id INT PRIMARY KEY,
  region VARCHAR(10),
  FOREIGN KEY (region) REFERENCES customers(region)
);

MySQL 8.0.46 created this table without complaint. MySQL 8.4.11 returned:

ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint 'orders7_ibfk_1' in the referenced table 'customers'

The same happened for a partial key. With CREATE TABLE p2 (a INT, b INT, PRIMARY KEY (a, b)), a foreign key referencing only p2(a) worked on 8.0 and failed with 6125 on 8.4. Referencing only p2(b) failed on both versions, because b is not the first column of any index (8.0 reported it as 1822).

The behavior is controlled by the restrict_fk_on_non_standard_key system variable, which is ON by default in 8.4:

SHOW VARIABLES LIKE 'restrict_fk_on_non_standard_key';

Turning it off in the session lets the old-style foreign key through, with a deprecation warning:

SET SESSION restrict_fk_on_non_standard_key = OFF;
CREATE TABLE orders7 (
  id INT PRIMARY KEY,
  region VARCHAR(10),
  FOREIGN KEY (region) REFERENCES customers(region)
);
SHOW WARNINGS;

Warning  6124  Foreign key 'orders7_ibfk_1' refers to non-unique key or partial key. This is deprecated and will be removed in a future release.

Use this only to get a legacy dump restored. The right fix is to reference a primary key or a unique key covering exactly the referenced columns:

ALTER TABLE customers ADD UNIQUE KEY uq_customers_email (email);

If the column genuinely is not unique (like region above), a foreign key is the wrong tool. Create a separate regions table with region as its primary key and point both tables at it.

The child side does not need an index prepared in advance. If the referencing column has no index, InnoDB creates one automatically, named after the constraint.

Cause 4: Parent Table Missing, Created Later, or Not InnoDB

A foreign key can only point at a table that exists and uses the InnoDB storage engine:

CREATE TABLE orders6 (
  id INT PRIMARY KEY,
  product_id INT,
  FOREIGN KEY (product_id) REFERENCES products(id)
);

ERROR 1824 (HY000): Failed to open the referenced table 'products'

The same error is returned when the parent exists but is a MyISAM table:

CREATE TABLE legacy (id INT PRIMARY KEY) ENGINE=MyISAM;
CREATE TABLE orders8 (
  id INT PRIMARY KEY,
  legacy_id INT,
  FOREIGN KEY (legacy_id) REFERENCES legacy(id)
);

ERROR 1824 (HY000): Failed to open the referenced table 'legacy'

Converting the parent fixes it: ALTER TABLE legacy ENGINE=InnoDB; followed by the same CREATE TABLE succeeded.

Watch out for the opposite case: a MyISAM child table. MySQL accepts the FOREIGN KEY clause without an error or warning and silently drops it. SHOW CREATE TABLE shows only an ordinary index, and no constraint is enforced. Check the engine of both tables:

SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop' AND ENGINE <> 'InnoDB';

Creation Order and Where 1215 Still Appears

Migration scripts and dumps usually create tables in alphabetical or arbitrary order, so a child can come before its parent. mysqldump handles this by setting foreign_key_checks = 0 at the top of the dump. With checks off, MySQL lets you create a foreign key to a table that does not exist yet:

SET foreign_key_checks = 0;
CREATE TABLE orders15 (
  id INT PRIMARY KEY,
  product_id INT,
  FOREIGN KEY (product_id) REFERENCES products(id)
);
SET foreign_key_checks = 1;

When the parent is created later, it must match the pending definition. If it does not, this is the classic, unhelpful error, and it happens on the parent table, not the child:

CREATE TABLE products (id BIGINT PRIMARY KEY);

ERROR 1215 (HY000): Cannot add foreign key constraint

We got exactly this message on both 8.0.46 and 8.4.11. products.id is BIGINT, but orders15.product_id is INT. Creating products with id INT PRIMARY KEY works. So if you see a bare 1215 on a CREATE TABLE that contains no foreign key at all, look for other tables that reference it:

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'shop'
  AND REFERENCED_TABLE_NAME = 'products';

Cause 5: SET NULL on a NOT NULL Column

ON DELETE SET NULL means "when the parent row is deleted, set the child column to NULL". That is impossible if the column is NOT NULL, so MySQL rejects the definition:

CREATE TABLE orders5 (
  id INT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
);

ERROR 1830 (HY000): Column 'customer_id' cannot be NOT NULL: needed in a foreign key constraint 'orders5_ibfk_1' SET NULL

Pick one: make the column nullable, or use ON DELETE CASCADE or RESTRICT instead.

Cause 6: Duplicate Constraint Name

Foreign key constraint names must be unique within a database, not just within a table. Copying a CREATE TABLE statement and forgetting to rename the constraint gives:

ERROR 1826 (HY000): Duplicate foreign key constraint name 'fk_ok'

A naming convention like fk_<child>_<parent> avoids it. See how to add a foreign key in MySQL for naming and syntax conventions.

Read the LATEST FOREIGN KEY ERROR Section

For the generic 1215, the useful detail is in the InnoDB monitor output:

SHOW ENGINE INNODB STATUS\G

Look for the LATEST FOREIGN KEY ERROR block near the top. After the failed CREATE TABLE products above, MySQL 8.4 printed:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2026-09-29 08:17:05 281472163835648 Error in foreign key constraint of table shop/orders15:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
  CONSTRAINT `orders15_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
The index in the foreign key in table is product_id
Please refer to http://dev.mysql.com/doc/refman/8.4/en/create-table-foreign-keys.html for correct foreign key definition.

How to read it:

• of table shop/orders15 names the child table whose constraint could not be satisfied. That is the table to compare against, even though your statement touched products.
• The CONSTRAINT line shows the exact columns on both sides.
• The explanation text is generic. It always says "no index ... or the data types ... do not match", so you still have to compare the column definitions yourself.

Two practical notes. The section only holds the most recent foreign key error on the whole server, and that includes data violations: after a later 1452 in our test, the block was replaced by a Transaction: report about the rejected row. So read it right after the failure. Also, errors caught by the SQL layer in MySQL 8 did not update the section in our tests (a 3780 left the older entry in place), so an entry you find there may be older than your current problem. Check the timestamp.

Step-by-Step Fix Script

Here is a complete walkthrough on a clean schema where the child table was created with the wrong type, the referenced email column has no unique key, and one order points at a customer that does not exist. Every result shown was produced on MySQL 8.4.11.

CREATE DATABASE fkdemo;
USE fkdemo;
 
CREATE TABLE customers (
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
 
CREATE TABLE orders (
  id          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  email       VARCHAR(100)
) ENGINE=InnoDB;
 
INSERT INTO customers (email) VALUES ('ann@example.com'), ('bob@example.com');
INSERT INTO orders (customer_id, email)
VALUES (1, 'ann@example.com'), (2, 'bob@example.com'), (7, 'eve@example.com');

Both foreign keys fail:

ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id);
-- ERROR 3780 (HY000): Referencing column 'customer_id' and referenced column 'id'
-- in foreign key constraint 'fk_orders_customer' are incompatible.
 
ALTER TABLE orders ADD CONSTRAINT fk_orders_email
  FOREIGN KEY (email) REFERENCES customers (email);
-- ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key
-- for constraint 'fk_orders_email' in the referenced table 'customers'

Step 1: Compare the Column Definitions

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND (TABLE_NAME, COLUMN_NAME) IN
      (('orders','customer_id'), ('customers','id'), ('orders','email'), ('customers','email'));

+------------+-------------+--------------+-------------+--------------------+--------------------+
| TABLE_NAME | COLUMN_NAME | COLUMN_TYPE  | IS_NULLABLE | CHARACTER_SET_NAME | COLLATION_NAME     |
+------------+-------------+--------------+-------------+--------------------+--------------------+
| customers  | id          | int unsigned | NO          | NULL               | NULL               |
| customers  | email       | varchar(100) | NO          | utf8mb4            | utf8mb4_0900_ai_ci |
| orders     | customer_id | int          | NO          | NULL               | NULL               |
| orders     | email       | varchar(100) | YES         | utf8mb4            | utf8mb4_0900_ai_ci |
+------------+-------------+--------------+-------------+--------------------+--------------------+

int vs int unsigned explains the 3780. The email columns match, so the 6125 must be about the index.

Step 2: Check the Indexes on the Parent

SELECT INDEX_NAME, NON_UNIQUE, SEQ_IN_INDEX, COLUMN_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customers'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

Only PRIMARY on id exists; there is no unique key on email.

Step 3: Find Orphan Rows Before Adding the Constraint

Fixing the definition is not enough if existing data violates the constraint. Look for child rows without a parent:

SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

+----+-------------+
| id | customer_id |
+----+-------------+
|  3 |           7 |
+----+-------------+

If you skip this step, the ALTER TABLE fails after the type fix with ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails, which is exactly what happened in our run. Decide whether to insert the missing parent, set the child value to NULL, or delete the orphan.

Step 4: Apply the Fixes

-- align the type with the parent key
ALTER TABLE orders MODIFY customer_id INT UNSIGNED NOT NULL;
 
-- repair the orphan (here: restore the missing customer)
INSERT INTO customers (id, email) VALUES (7, 'eve@example.com');
 
-- give the referenced column a unique key
ALTER TABLE customers ADD UNIQUE KEY uq_customers_email (email);
 
-- now both constraints succeed
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_email
  FOREIGN KEY (email) REFERENCES customers (email);

Step 5: Verify

SELECT k.CONSTRAINT_NAME, k.TABLE_NAME, k.COLUMN_NAME,
       k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME,
       r.UPDATE_RULE, r.DELETE_RULE
FROM information_schema.KEY_COLUMN_USAGE k
JOIN information_schema.REFERENTIAL_CONSTRAINTS r
  ON r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
 AND r.CONSTRAINT_NAME   = k.CONSTRAINT_NAME
WHERE k.TABLE_SCHEMA = DATABASE()
  AND k.REFERENCED_TABLE_NAME IS NOT NULL;

+--------------------+------------+-------------+-----------------------+------------------------+-------------+-------------+
| CONSTRAINT_NAME    | TABLE_NAME | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | UPDATE_RULE | DELETE_RULE |
+--------------------+------------+-------------+-----------------------+------------------------+-------------+-------------+
| fk_orders_customer | orders     | customer_id | customers             | id                     | NO ACTION   | NO ACTION   |
| fk_orders_email    | orders     | email       | customers             | email                  | NO ACTION   | NO ACTION   |
+--------------------+------------+-------------+-----------------------+------------------------+-------------+-------------+

If you prefer to do the column comparison visually, a client such as Chat2DB (opens in a new tab) shows both table structures side by side and lets you edit the column type and foreign key in a form, then previews the generated ALTER TABLE before running it.

Quick Checklist

When a foreign key refuses to be created, go through this list in order:

1. Is the error really about the definition? A 1452 means the data is the problem, not the schema.
2. Do both columns have exactly the same type, size and sign? INT is not INT UNSIGNED, and INT is not BIGINT.
3. For strings, do the character set and collation match? VARCHAR length may differ.
4. Is there a primary key or unique key on exactly the referenced columns? MySQL 8.4 no longer accepts non-unique or partial keys by default.
5. Does the parent table exist, and are both tables InnoDB?
6. Does ON DELETE SET NULL or ON UPDATE SET NULL point at a NOT NULL column?
7. Is the constraint name already used elsewhere in the schema?
8. For a bare 1215 on a table without its own foreign keys, which existing child table references it? Check LATEST FOREIGN KEY ERROR.

Summary

ERROR 1215 is the old catch-all. On MySQL 8.0 and 8.4, most foreign key definition problems surface as 3780 (incompatible columns), 1822 or 6125 (missing index or unique key), 1824 (missing or non-InnoDB parent) or 1830 (SET NULL on a NOT NULL column), and these messages name the columns involved. The bare 1215 still appears when a parent table is created after a child that references it and the column types do not match. In every case the fix is the same: make the definitions line up exactly, give the parent a unique key on the referenced columns, clean up orphan rows, and add the constraint again.