MySQL ERROR 1215: Cannot Add Foreign Key Constraint - Checklist

Error 1215 appears when CREATE TABLE or ALTER TABLE defines a foreign key that InnoDB cannot accept. The message is famously unspecific, but the list of causes is short and each one can be checked.

ERROR 1215 (HY000): Cannot add foreign key constraint

Get the real reason first

Run the statement again, then ask InnoDB what it disliked:

SHOW ENGINE INNODB STATUS\G

Find the section LATEST FOREIGN KEY ERROR. It prints the table, the constraint and a hint. Look at the exact definitions of both tables side by side:

SHOW CREATE TABLE users\G
SHOW CREATE TABLE orders\G

The checklist

1. The column types must match exactly

Same base type, same size and, for integers, the same signedness. This is the most common cause, particularly in Laravel and similar frameworks:

-- users.id is BIGINT UNSIGNED
ALTER TABLE orders
  ADD COLUMN user_id INT,                       -- wrong type
  ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id);

INT versus BIGINT, or INT versus INT UNSIGNED, will fail. Fix the child column to match the parent:

ALTER TABLE orders MODIFY user_id BIGINT UNSIGNED NOT NULL;

In Laravel migrations, use $table->foreignId('user_id')->constrained();, which creates an unsigned big integer matching the default id() column. An older table that used increments() creates an unsigned INT and needs unsignedInteger('user_id') instead.

2. String columns need the same charset and collation

For VARCHAR keys, both columns must use the same character set and collation. A table created under utf8mb4_0900_ai_ci and another under utf8mb4_unicode_ci cannot reference each other. Convert one:

ALTER TABLE child CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. The parent column needs an index

The referenced column must be the first column of an index, usually a PRIMARY KEY or UNIQUE key. MySQL creates an index on the child column automatically if none exists, but not on the parent.

4. Both tables must be InnoDB

MyISAM tables silently ignore foreign key definitions in some versions and cannot be referenced in others. Check with SHOW TABLE STATUS LIKE 'users' and convert with ALTER TABLE users ENGINE=InnoDB;.

5. The parent table must already exist

In a migration or dump, create the parent first. Circular references need one table created without the constraint, then ALTER TABLE ... ADD CONSTRAINT after both exist. For dump imports, wrap everything with:

SET FOREIGN_KEY_CHECKS = 0;
-- source dump here
SET FOREIGN_KEY_CHECKS = 1;

This setting is per session and it does not validate existing rows, so check consistency afterward.

6. Existing rows violate the constraint

Adding a key to a table that already has orphaned child rows fails. Find them:

SELECT o.id, o.user_id
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE o.user_id IS NOT NULL AND u.id IS NULL;

Delete them, set them to NULL if the column is nullable, or create the missing parents. On a plain ALTER TABLE this often shows as error 1452 ("Cannot add or update a child row") rather than 1215.

7. ON DELETE SET NULL on a NOT NULL column

A SET NULL action requires the child column to be nullable. Either allow NULL or use CASCADE, RESTRICT or NO ACTION.

8. Duplicate constraint names and generated or partitioned columns

Constraint names must be unique per database, not just per table. Reusing fk_user on two tables fails. Foreign keys are not supported on partitioned InnoDB tables, and certain generated columns cannot be used in them.

9. Prefix indexes and key length

A parent key built on a column prefix (name(10)) cannot be referenced. The referenced columns must be complete.

Verify and keep it working

After the fix, list your constraints:

SELECT constraint_name, table_name, referenced_table_name
FROM information_schema.referential_constraints
WHERE constraint_schema = 'shop';

Test by inserting a child row with a nonexistent parent id; you should see error 1452. In deployments, run migrations on a copy of production data first, because real data exposes orphan rows that an empty test database never does. Also monitor the pages that depend on the new tables with an Uptime Monitor.

Next steps

• Seeing duplicate key problems on insert? Read MySQL error 1062.
• Fix syntax problems in the DDL with MySQL error 1064.
• Missing table in a migration? See MySQL error 1146.
• Run a Website Health Check after deploying schema changes.

FAQ

What is the difference between errors 1215 and 1452?

1215 means the constraint definition cannot be created. 1452 means a particular row violates an existing constraint when you insert or update it.

Do I need an index on the child column?

InnoDB creates one automatically if there is none. The parent column, though, must already be indexed.

Can I disable foreign key checks permanently?

You can set foreign_key_checks off for a session, but it is meant for imports and migrations. Leaving constraints unenforced lets orphan rows accumulate.

Why does it work in MariaDB but fail in MySQL 8?

Collation defaults differ between the two. MySQL 8 may create tables with utf8mb4_0900_ai_ci, so declare the collation explicitly on every table.

This page is general information written by our editorial team and updated when documentation changes. Servers and providers differ, so test changes safely first. See our editorial policy and disclaimer.