1215ERRORTier 2 — Caution✅ HIGH confidence

Cannot add foreign key constraint

Category: SchemaVersions: All MariaDB / MySQL versions

What this means

Error 1215 is returned when an ALTER TABLE or CREATE TABLE statement cannot create a foreign key constraint. The error message is unfortunately generic — it does not say why the FK failed. The actual reason must be found by running SHOW ENGINE INNODB STATUS immediately after the error.

Why it happens

1The referenced parent column does not have an index (InnoDB requires an index on the referenced column)
2The child column and parent column have different data types or character sets
3The referenced parent table uses a different storage engine (e.g., MyISAM does not support FK)
4The parent table or column does not exist
5The child column is NOT NULL but no DEFAULT is defined and the parent has no row for the initial child rows

How to reproduce

A foreign key references a column with a different data type.

trigger — this will ERROR

CREATE TABLE parent (id INT PRIMARY KEY);
CREATE TABLE child (
  id INT PRIMARY KEY,
  parent_id BIGINT,  -- type mismatch: INT vs BIGINT
  FOREIGN KEY (parent_id) REFERENCES parent(id)
);

ERROR 1215 (HY000): Cannot add foreign key constraint

Fix 1: Match data types exactly between child and parent columns

When the type mismatch is the cause.

fix

CREATE TABLE parent (id INT UNSIGNED PRIMARY KEY);
CREATE TABLE child (
  id INT PRIMARY KEY,
  parent_id INT UNSIGNED,  -- exact match: INT UNSIGNED
  FOREIGN KEY (parent_id) REFERENCES parent(id)
);

Why this works

InnoDB requires the child foreign key column and the referenced parent column to have identical data types, including signedness (INT vs INT UNSIGNED are different types).

Fix 2: Run SHOW ENGINE INNODB STATUS to get the real error

When the cause of the error is not obvious.

fix

SHOW ENGINE INNODB STATUS\G
-- Look for: LATEST FOREIGN KEY ERROR

Why this works

SHOW ENGINE INNODB STATUS's LATEST FOREIGN KEY ERROR section contains the detailed reason the FK constraint failed, including the specific column mismatch or missing index.

What not to do

✗

Use SET foreign_key_checks=0 to bypass the error during migrations

Why it's wrong: Disabling FK checks allows invalid references to be inserted. When FK checks are re-enabled, the existing orphaned rows will violate the constraint, and subsequent DML on those rows will fail.

Version notes

MariaDB 10.2+InnoDB now supports virtual columns in foreign keys.

Sources

📚 Official docs: https://mariadb.com/kb/en/foreign-keys/

🔧 Source ref: MariaDB Server error code 1215 / ER_CANNOT_ADD_FOREIGN

📖 Further reading: MariaDB foreign keys

📖 Further reading: SHOW ENGINE INNODB STATUS

Confidence assessment

✅ HIGH confidence

Well-documented. The SHOW ENGINE INNODB STATUS approach for diagnosis is standard MariaDB/MySQL practice.