MySQL 1215: Foreign Key Constraint Fix Steps That Work

What Error 1215 Actually Means

MySQL throws 1215 when a foreign key definition doesn't match the parent table's structure. It's a catch-all error, which is why so many people get stuck. The message gives you almost nothing, so you have to check a few things yourself.

Common triggers: you copy a table definition from another database, you change a column type on the parent table but forget the child, or you're using different collations between tables. Also happens when you reference a column that isn't a primary key or unique index.

Let's walk through fixes in order of time required. Start with the first one, test, and if it still fails, move down.

Fix 1: The 30-Second Check — Column Types Must Match Exactly

This is the most common cause. The foreign key column and the referenced column must be the same data type, including length and unsigned attribute.

Example: If the parent table has id INT UNSIGNED, your child column must be INT UNSIGNED. If the parent has id BIGINT, your child must be BIGINT. No exceptions. A common mistake is having the child as INT and the parent as INT UNSIGNED — that fails instantly.

Here's how to check both columns:

SHOW CREATE TABLE parent_table;\nSHOW CREATE TABLE child_table;\n

Look at the column definitions. Compare them side by side. Adjust the child column to match exactly. Then retry your ALTER TABLE or CREATE TABLE statement.

Fix 2: The 5-Minute Fix — Add Missing Indexes

MySQL requires an index on the foreign key column in the child table. It also requires the referenced column in the parent to be a primary key or have a unique index. If either is missing, you get 1215.

For the child table, add an index on the column you're using as the foreign key. You can do it in the same CREATE TABLE statement or with a separate ALTER:

ALTER TABLE child_table ADD INDEX idx_parent_id (parent_id);\n

For the parent table, if the column isn't already a primary key, add a unique index:

ALTER TABLE parent_table ADD UNIQUE INDEX idx_unique_id (id);\n

After adding indexes, try your foreign key creation again. Many people skip this because they assume the column already has an index. Double-check with:

SHOW INDEX FROM child_table;\nSHOW INDEX FROM parent_table;\n

If you see no rows for the child column, that's your problem.

Fix 3: The 15+ Minute Fix — Collation and Engine Mismatch

If the first two fixes didn't work, the issue is likely collation or storage engine. Both tables must use the same storage engine (InnoDB is required for foreign keys) and the same collation for the referenced columns.

Collation mismatch happens often when you create tables with different default character sets. For example, one table uses utf8mb4_unicode_ci and the other uses utf8mb4_general_ci. Even if the column types match, collation differences cause 1215.

Check the collation of both tables:

SELECT table_name, table_collation FROM information_schema.tables WHERE table_schema = 'your_database';\n

If they differ, change one table to match the other. For instance:

ALTER TABLE child_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n

Also confirm both tables are InnoDB:

SELECT table_name, engine FROM information_schema.tables WHERE table_schema = 'your_database';\n

If you see MyISAM, convert to InnoDB:

ALTER TABLE child_table ENGINE=InnoDB;\n

After these changes, retry your foreign key. This fix usually solves the stubborn cases.

Bonus: Example That Causes 1215 and How to Fix It

Let's say you have a parent table users with id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY. You create a child table orders with user_id INT (not unsigned). That fails. The fix is to change user_id to INT UNSIGNED.

Another example: You reference a column that isn't indexed. Parent table categories has name VARCHAR(50) but no index. You try to create a foreign key on category_name in another table. That fails. You need to add a unique index on categories.name first.

When to Skip Further Troubleshooting

If you've checked types, indexes, collation, and engine, and it still fails, there might be a data issue. Check if the child table has rows with values that don't exist in the parent. For example, if the parent has IDs 1, 2, 3 but the child has a 4, the foreign key can't be added. Clean up the data:

SELECT * FROM child_table WHERE parent_id NOT IN (SELECT id FROM parent_table);\n

Delete or update those rows, then retry. That's rare but it happens.

Follow these steps in order, and you'll resolve 1215 without guessing.