Fix ERROR 1215 (HY000): Cannot add foreign key constraint in MySQL

What's happening

You're trying to add a foreign key and MySQL throws back:

ERROR 1215 (HY000): Cannot add foreign key constraint

Frustratingly vague. MySQL won't tell you why it failed — there are at least five distinct root causes, and they all produce the exact same message. This guide tackles each one so you can match your error output to a concrete fix.

Step 1: Get the actual error reason

Don't guess. Run this immediately after the failed statement:

SHOW ENGINE INNODB STATUS\G

Scroll to the LATEST FOREIGN KEY ERROR section. You'll see something like:

Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.

Now you have something to work with. Match it to one of the causes below.

Common causes and fixes

Cause 1: Data type mismatch

The single most common culprit. The child column and parent column must have exactly the same data type — including size and signedness. Even one mismatch kills the constraint.

-- Parent table
CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
);

-- This FAILS — INT vs INT UNSIGNED
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,  -- missing UNSIGNED
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Fix: match the type exactly.

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED,  -- matches users.id
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Always double-check the parent column first:

SHOW CREATE TABLE users;

Cause 2: Referenced column has no index

MySQL requires the referenced column to be a PRIMARY KEY or have a UNIQUE index. Reference a plain column with no index and you'll hit this error every time.

-- users.email has no index → FK fails
CREATE TABLE sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_email VARCHAR(255),
  FOREIGN KEY (user_email) REFERENCES users(email)  -- error!
);

Add an index to the parent column first, then retry:

ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

Cause 3: Storage engine is not InnoDB

Foreign keys are an InnoDB-only feature. MyISAM simply doesn't support them — the constraint will fail outright or get silently ignored depending on the MySQL version.

SHOW CREATE TABLE users;

Spot ENGINE=MyISAM in the output? Convert both tables:

ALTER TABLE users ENGINE=InnoDB;
ALTER TABLE orders ENGINE=InnoDB;

Want all new tables to default to InnoDB without thinking about it?

SET default_storage_engine=InnoDB;

Cause 4: Character set or collation mismatch

Both columns are VARCHAR — looks fine on the surface. But if one uses utf8 and the other uses utf8mb4, or they have different collations, MySQL treats them as incompatible types.

-- Check current collations
SHOW CREATE TABLE users;
SHOW CREATE TABLE orders;

If you see something like utf8_general_ci on one side and utf8mb4_unicode_ci on the other, align them:

ALTER TABLE orders MODIFY user_email VARCHAR(255)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Cause 5: Existing data violates the constraint

Adding a FK to a table that already has rows? MySQL validates every single one. Any user_id in orders that has no matching row in users will block the entire operation.

-- Find orphan rows
SELECT o.id, o.user_id
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

Clean up the orphans first — delete them, set them to NULL (if the column allows it), or insert the missing parent records. Then retry.

Cause 6: Referenced table doesn't exist yet

Order matters in migrations and dump files. Create orders before users exists and the FK fails immediately — there's nothing to reference yet.

Reorder your CREATE TABLE statements so parent tables come first. When importing a dump, wrap everything with FOREIGN_KEY_CHECKS disabled to sidestep ordering issues entirely:

SET FOREIGN_KEY_CHECKS=0;
-- all your CREATE TABLE statements
SET FOREIGN_KEY_CHECKS=1;

Re-enable it at the end so constraints are actually enforced going forward.

Verification: confirm the fix worked

Once the FK is added, confirm it's active in information_schema:

SELECT
  CONSTRAINT_NAME,
  TABLE_NAME,
  COLUMN_NAME,
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_database_name'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

Your new constraint should appear in the results. Then do a quick smoke test:

-- This should fail with FK violation, confirming constraint is active
INSERT INTO orders (user_id) VALUES (99999);  -- user 99999 doesn't exist
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

ERROR 1452 here is the result you want — it proves the foreign key is enforcing referential integrity.

Quick reference checklist

• Run SHOW ENGINE INNODB STATUS\G to get the real error message
• Both columns must have identical types (including UNSIGNED)
• Referenced column must be a PK or have a UNIQUE/INDEX
• Both tables must use ENGINE=InnoDB
• Character set and collation must match on VARCHAR columns
• No orphan rows in the child table before adding the FK
• Parent table must exist before the child table references it

The pattern behind these errors

ERROR 1215 is a catch-all. MySQL stops at the first violation it finds and reports the same generic message regardless of the actual cause. One habit worth building: run SHOW ENGINE INNODB STATUS\G immediately after every failure. That single command cuts debugging time from 30 minutes to about 2.

For new schemas, define parent tables first. Use consistent types across related columns — INT UNSIGNED for any auto-increment PK. Pick one character set for the whole database from day one; utf8mb4 is the right default in 2024+. Most foreign key errors aren't mysterious. They're the result of small inconsistencies that crept in over time and finally hit a constraint that cares.