MySQL Error 1215: Cannot Add Foreign Key Constraint

MySQL Error 1215 (HY000, ER_CANNOT_ADD_FOREIGN) means the server could not create a foreign key constraint. The message, Cannot add foreign key constraint, does not identify the cause. It commonly appears while creating a table or adding a constraint with ALTER TABLE. MySQL 8.4 has specific errors for common cases, including Error 1822 for a missing index in the referenced table and Error 3780 for incompatible columns. If the message names a missing parent-table index, see how to fix MySQL Error 1822; if it names incompatible child and parent columns, see how to fix MySQL Error 3780. Check the full message and server version before changing a schema. See the MySQL 8.0 error reference for Error 1215 and the MySQL 8.4 error reference for newer diagnostics.

Start with matching parent and child columns

The foreign-key column in a child table must be compatible with the referenced column in the parent. For integer columns, match the type size and signedness. For nonbinary character columns, use the same character set and collation. The parent and child tables must also use the same storage engine; InnoDB is the usual choice. These rules are described in the MySQL foreign-key requirements.

For example, this definition uses INT UNSIGNED on both sides and gives the referenced column a primary key:

CREATE TABLE departments (
  department_id INT UNSIGNED NOT NULL,
  department_name VARCHAR(100) NOT NULL,
  PRIMARY KEY (department_id)
) ENGINE = InnoDB;

CREATE TABLE employees (
  employee_id BIGINT UNSIGNED NOT NULL,
  department_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (employee_id),
  KEY ix_employees_department_id (department_id),
  CONSTRAINT fk_employees_department
    FOREIGN KEY (department_id)
    REFERENCES departments (department_id)
) ENGINE = InnoDB;

If one column were INT and the other INT UNSIGNED, align the definitions before adding the constraint. When altering a table, first check whether existing data can be converted safely; do not change signedness or width blindly on a production table.

Check the common causes

Compare both complete table definitions and check these details:

1. Column types match. Integer width and signedness must match. For character columns, compare character set and collation as well as the type. Column names do not have to be the same.
2. Both tables use the same supported engine. For InnoDB foreign keys, make sure neither table is using a different engine such as MyISAM. Temporary tables cannot participate in a foreign-key relationship.
3. The referenced columns have a suitable index. In normal designs, reference a PRIMARY KEY or UNIQUE key. For a composite key, the referenced columns must be the leading index columns in the same order. The child table also needs an index beginning with its foreign-key columns; InnoDB creates one automatically if needed.
4. The referenced table and columns are the ones you intend. Confirm the active database, table names, column order, and spelling. Create the parent table before the child table when building a schema from scratch.
5. The column and table features are supported. InnoDB foreign keys cannot use TEXT or BLOB columns, because those types require prefix indexes. User-partitioned InnoDB tables do not support foreign keys.

Run SHOW CREATE TABLE on both tables instead of relying on an ORM model or migration file; the live definitions may differ:

SHOW CREATE TABLE departments\G
SHOW CREATE TABLE employees\G

For a composite foreign key, check that the child and parent column lists pair up in the same order, and that the supporting indexes begin with those same columns. For example, (tenant_id, department_id) is a different key order from (department_id, tenant_id).

Read the detailed InnoDB error

When the error text does not reveal the mismatch, inspect InnoDB’s most recent foreign-key diagnostic immediately after the failed statement:

SHOW ENGINE INNODB STATUS\G

Find the LATEST FOREIGN KEY ERROR section. It can show the table, constraint, and reason InnoDB rejected the definition. This status contains the latest error, so run the command right after the failure before another foreign-key operation replaces the useful context. You can also run SHOW WARNINGS; immediately after the failing statement for additional server diagnostics.

Error 1215 versus Errors 1452 and 1451

Error 1215 concerns creating a foreign-key definition. Error 1452 occurs when an INSERT or UPDATE adds a child-row value that has no matching parent row; see how to fix MySQL Error 1452. Error 1451 is raised when a parent row cannot be updated or deleted because child rows still reference it. For the full constraint behavior, see the MySQL foreign-key guide.

Do not disable foreign_key_checks as a way to hide a malformed definition. It does not make incompatible column types or unsupported table definitions valid, and turning checks back on does not scan rows that were added while checks were disabled.