MySQL 'Cannot add foreign key constraint' Error Fix

When does this error hit you?

You're running an ALTER TABLE or CREATE TABLE statement that includes a FOREIGN KEY clause. Maybe you're trying to link an orders table to a customers table. The exact trigger is often something like:

ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

And MySQL spits back: Cannot add foreign key constraint. That's error 1215, and it's one of the most frustrating because the error message doesn't tell you why.

Root cause in plain English

MySQL is a stickler for rules when it comes to foreign keys. The parent column (the one you're referencing) and the child column (the one in your table) must match exactly. I mean exactly. Not close enough. Same data type, same length, same collation, same ENGINE, same index. If any of these don't line up, MySQL refuses the constraint. It's not being difficult—it's protecting your data integrity.

The most common culprits I've seen in 12 years of support:

• Data type mismatch – INT vs BIGINT, or even INT(11) vs INT(10) (yes, that matters in older versions).
• Collation mismatch – The parent table uses utf8mb4_unicode_ci but the child uses latin1_swedish_ci.
• ENGINE mismatch – One table is InnoDB, the other is MyISAM. Foreign keys only work with InnoDB.
• Missing index on parent – The referenced column must be indexed (or be a primary key).
• Self-referencing table – You're referencing a column in the same table, but the column isn't indexed.

How to fix it step by step

I'm going to walk you through a systematic check. Don't skip steps—they're fast and each one rules out a common cause.

Step 1: Check the ENGINE of both tables

Foreign keys only work with InnoDB. Run:

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME IN ('parent_table', 'child_table');

What you should see: Both tables should show InnoDB. If one shows MyISAM or anything else, that's your problem. Convert the offending table with:

ALTER TABLE table_name ENGINE=InnoDB;

After that, retry your constraint.

Step 2: Compare data types and lengths

Run a SHOW CREATE TABLE on both tables:

SHOW CREATE TABLE parent_table;
SHOW CREATE TABLE child_table;

Look at the column definitions for the referenced and referencing columns. They must match exactly. Example mismatch: Parent has id INT(11) UNSIGNED but child has customer_id INT(11) (missing UNSIGNED). Or parent has id BIGINT(20) but child has customer_id INT(11).

Fix: Alter the child column to match. For example:

ALTER TABLE child_table MODIFY COLUMN customer_id INT(11) UNSIGNED;

Step 3: Confirm the parent column is indexed

The referenced column must have an index (usually it's the primary key, but can be a unique key or even a normal index). Check with:

SHOW INDEX FROM parent_table WHERE Column_name = 'id';

If no rows return, you need to add an index. But if it's the primary key, it's already indexed. If it's not a primary key, add an index:

ALTER TABLE parent_table ADD INDEX (id);

Step 4: Check collation

This one trips up a lot of people. Run:

SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME IN ('parent_table', 'child_table');

If the collations differ, you need to make them match. The safest approach is to convert both tables to the same collation (usually utf8mb4_unicode_ci):

ALTER TABLE parent_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE child_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Important: This changes all string columns in the table. It's safe, but test on a backup first.

Step 5: Verify the referenced column is not a partial key

If your parent column is part of a composite index (e.g., index on (a, b) but you're referencing only b), MySQL won't allow it. The referenced column must be the first column in the index. If it's not, you need to add a separate index on just that column.

What to check if it still fails

If you've gone through all five steps and the error persists, here are the less common but real gremlins:

• You're referencing a column that's part of a foreign key already – MySQL doesn't allow overlapping foreign keys in all cases. Drop any existing foreign key on the child table and try again.
• You're running an old MySQL version – Versions before 5.5 had stricter rules. If you're on 5.1 or 5.0, consider upgrading. I've seen 5.1 reject foreign keys that 5.7 accepts without issue.
• The referenced column has a default value that's not a valid reference – This is rare, but if the parent column has a default that doesn't exist, it's fine; the child doesn't inherit that. So ignore this unless you're doing something weird.
• You're trying to reference a column in a different database – Yes, you can do that, but both databases must have the same default character set and collation. Check both database-level defaults:

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'your_database_name';

If you still can't figure it out, post the full SHOW CREATE TABLE output for both tables and your exact ALTER TABLE statement on a forum like Stack Overflow. Include your MySQL version (SELECT VERSION();). I promise someone will spot what you missed within an hour.