How to Resolve "Error 1215 (HY000): Cannot Add Foreign Key Constraint"

Creating or altering a table to add a foreign key fails with a vague error that doesn't say what's actually wrong. Error 1215 is famously unhelpful on its own — MySQL knows the constraint can't be added but doesn't tell you why in the same message, so you have to dig a bit further to find the real cause.

The Problem

An otherwise reasonable-looking ALTER TABLE or CREATE TABLE statement fails:

mysql> ALTER TABLE orders ADD CONSTRAINT fk_customer
    -> FOREIGN KEY (customer_id) REFERENCES customers(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

The error gives no specifics, so the next step is always to ask MySQL directly what went wrong:

mysql> SHOW ENGINE INNODB STATUS\G

------------------------
LATEST FOREIGN KEY ERROR
------------------------
Cannot add foreign key constraint fk_customer
Type of customer_id in orders doesn't match type of id in customers

Why It Happens

InnoDB enforces several strict prerequisites before it will allow a foreign key constraint, and violating any one of them produces the exact same generic 1215 error. The most common causes, roughly in order of frequency:

• Column type mismatch — the referencing and referenced columns must have the same data type, including signedness (INT vs INT UNSIGNED counts as a mismatch) and, for strings, the same character set and collation.
• Missing index on the referenced column — the column being referenced (usually a primary key, but not always) needs an index; InnoDB can't enforce the constraint efficiently without one.
• Storage engine mismatch — both tables need to use InnoDB. Foreign keys are silently ignored (in older MySQL) or rejected (in current versions) if either table uses MyISAM or another engine that doesn't support them.
• Existing data violates the constraint — if the referencing table already has rows with values not present in the referenced table, MySQL won't let you add the constraint until that data is cleaned up.
• Referenced table or column doesn't actually exist, often due to a typo in the table or column name.

The Fix

Always check SHOW ENGINE INNODB STATUS first — it's the only place MySQL tells you the specific reason, and guessing without it wastes time:

SHOW ENGINE INNODB STATUS\G

If it's a type mismatch, compare both columns exactly:

SHOW CREATE TABLE customers;
SHOW CREATE TABLE orders;

Align the referencing column's type precisely with the referenced one, including signedness:

ALTER TABLE orders MODIFY customer_id BIGINT UNSIGNED;

If it's a missing index on the referenced column, add one (primary keys already have this, but a non-primary-key reference target might not):

ALTER TABLE customers ADD INDEX idx_customers_id (id);

If it's an engine mismatch, convert both tables to InnoDB:

ALTER TABLE customers ENGINE=InnoDB;
ALTER TABLE orders ENGINE=InnoDB;

If existing data already violates the intended constraint, find and fix the orphaned rows before adding it:

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

Once that's clean, retry the original constraint:

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

Still Not Working?

If everything above checks out and it's still failing, verify character set and collation match too, not just the numeric or string type — a VARCHAR(36) column with utf8mb4 can fail against another VARCHAR(36) using plain utf8, since InnoDB treats mismatched collations the same way it treats mismatched types:

SHOW FULL COLUMNS FROM customers WHERE Field='id';
SHOW FULL COLUMNS FROM orders WHERE Field='customer_id';

Bring both columns to the exact same character set and collation if they differ, then retry the constraint once more.

It's also worth checking parent and child table row formats and whether either table has an unusually large number of existing foreign keys already, since InnoDB imposes practical limits on foreign key relationships per table that can occasionally surface as this same generic error on complex schemas. If none of the more common causes above explain the failure, try creating the constraint on a fresh, minimal test table with the exact same column definitions as your real tables — if it succeeds there, the problem is likely something specific to the existing tables' current state (leftover orphaned constraints, a partial previous migration) rather than the column definitions themselves:

CREATE TABLE test_customers (id BIGINT UNSIGNED PRIMARY KEY);
CREATE TABLE test_orders (
    id BIGINT UNSIGNED PRIMARY KEY,
    customer_id BIGINT UNSIGNED,
    FOREIGN KEY (customer_id) REFERENCES test_customers(id)
);

If this minimal version works without issue, compare it field by field against your real tables using SHOW CREATE TABLE on both, looking specifically for anything that differs beyond the column names themselves — a subtle difference in row format, an existing but broken constraint with the same name, or a table-level option that isn't obvious from a casual read of the schema.