How to fix ERROR 1215: Cannot add foreign key constraint in MySQL

MySQLINTERMEDIATEMEDIUMUpdated Dec 25, 2025· 185 views

ERROR 1215 occurs when MySQL cannot create a foreign key constraint due to mismatched data types, missing indexes, or storage engine issues. The fix involves verifying data types match exactly, ensuring the referenced column has an index, and confirming both tables use InnoDB.

Quick fix

Fixes ERROR 1215: Cannot add foreign key constraint

SHOW TABLE STATUS WHERE name IN ('parent_table', 'child_table');
  1. Check the storage engine is InnoDB
  2. Verify the parent table exists with correct column name
  3. Compare exact data types of both columns
  4. Verify the referenced column has an index
mysql — error
SHOW TABLE STATUS WHERE name IN ('parent_table', 'child_table');
ERROR 1215: Cannot add foreign key constraint

What this error means

ERROR 1215 (HY000) is triggered when you attempt to create a foreign key constraint, but MySQL cannot establish the relationship between tables. Unlike most errors, MySQL does not specify the exact reason why the constraint failed, making this error particularly frustrating to debug. This error occurs exclusively in InnoDB tables since MyISAM does not support foreign keys. The constraint cannot be created due to structural incompatibilities between the foreign key column and the referenced column it points to.

Typical symptoms

  • ALTER TABLE or CREATE TABLE statements fail when adding foreign key constraints
  • The error message provides no details about which specific constraint requirement is violated
  • Manual verification shows tables and columns appear to match correctly
  • Foreign key constraints work for some column pairs but fail for others in the same database
  • Errors occur intermittently when importing databases or creating schemas in different orders

Common causes

  1. Data type mismatch between the foreign key column and the referenced primary/unique key column (e.g., INT vs. INT UNSIGNED, or different INTEGER sizes)
  2. Referenced column lacks a primary key or unique index required for the constraint
  3. Character set or collation differs between VARCHAR or TEXT columns in the foreign key relationship
  4. Referenced parent table does not exist or table names are spelled incorrectly
  5. Storage engine is MyISAM instead of InnoDB (MyISAM does not support foreign keys)
  6. Foreign key constraint uses ON DELETE SET NULL or ON UPDATE SET NULL on a NOT NULL column
  7. Parent table is partitioned (foreign keys cannot reference partitioned tables)
  8. Referenced column is a virtual or generated column rather than a stored column
  9. Missing or incorrect column reference in the REFERENCES clause

How to fix "ERROR 1215: Cannot add foreign key constraint"

1Check the storage engine is InnoDB

First, verify that both your parent and child tables use the InnoDB storage engine. Only InnoDB supports foreign key constraints.

sql
SHOW TABLE STATUS WHERE name IN ('parent_table', 'child_table');

If the Engine column shows MyISAM, convert the table:

sql
ALTER TABLE child_table ENGINE=InnoDB;
ALTER TABLE parent_table ENGINE=InnoDB;
2Verify the parent table exists with correct column name

Ensure the referenced parent table exists and contains the column you are referencing. Tables must be created before creating the foreign key constraint.

sql
SHOW CREATE TABLE parent_table;
DESCRIBE parent_table;

Verify the column name in the REFERENCES clause matches exactly, including case sensitivity.

3Compare exact data types of both columns

The foreign key column and the referenced column must have identical data types, including size and sign.

sql
DESCRIBE parent_table;
DESCRIBE child_table;

Common mismatches to watch for:
- INT vs. INT UNSIGNED
- INT vs. BIGINT
- INT vs. SMALLINT
- VARCHAR(100) with different collations

If the types don't match, modify the child table column to match the parent:

sql
ALTER TABLE child_table MODIFY COLUMN child_id INT UNSIGNED;
4Verify the referenced column has an index

The referenced column must have either a PRIMARY KEY or a UNIQUE index. Check existing indexes:

sql
SHOW INDEX FROM parent_table;

If the referenced column doesn't have an index, add one:

sql
ALTER TABLE parent_table ADD UNIQUE KEY uk_parent_id (parent_id);

Or if it should be the primary key:

sql
ALTER TABLE parent_table ADD PRIMARY KEY (parent_id);
5Check character set and collation for string columns

For VARCHAR or TEXT columns, both the foreign key column and referenced column must use the same character set and collation.

sql
SHOW CREATE TABLE parent_table;
SHOW CREATE TABLE child_table;

Look for CHARSET and COLLATE in the column definitions. If they differ, modify the child table column:

sql
ALTER TABLE child_table MODIFY COLUMN child_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
6Diagnose with SHOW ENGINE INNODB STATUS

If the above steps don't resolve the issue, run this diagnostic command to see detailed error information:

sql
SHOW ENGINE INNODB STATUS;

Look for the "LATEST FOREIGN KEY ERROR" section, which provides more specific details about why the constraint failed. This output is often more helpful than ERROR 1215 itself.

7Create the foreign key constraint

Once all prerequisites are met (matching data types, valid index on parent column, InnoDB engine), create the constraint:

sql
ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

Or during table creation:

sql
CREATE TABLE child_table (
  child_id INT UNSIGNED NOT NULL,
  CONSTRAINT fk_child_parent FOREIGN KEY (child_id)
    REFERENCES parent_table (parent_id)
    ON DELETE CASCADE
);

Advanced notes

Partitioned tables cannot be referenced by foreign key constraints; if your parent table uses partitioning, you must remove it by merging all partitions back into a single table. Virtual or generated columns (created with AS expression) cannot be referenced by foreign keys; the column must be a stored column with actual data. When using ON DELETE SET NULL or ON UPDATE SET NULL, the child column must be defined as nullable (without NOT NULL). For bulk operations on large tables, consider using tools like pt-online-schema-change to avoid locking the entire table during index creation or engine conversion. Some MySQL versions have known bugs with foreign key constraints and fulltext indexes on the same table (see MySQL bugs #89219 and #89220); upgrading to the latest MySQL version may resolve such edge cases.

Frequently asked questions

What does "ERROR 1215: Cannot add foreign key constraint" mean?
ERROR 1215 (HY000) is triggered when you attempt to create a foreign key constraint, but MySQL cannot establish the relationship between tables. Unlike most errors, MySQL does not specify the exact reason why the constraint failed, making this error particularly frustrating to debug. This error occurs exclusively in InnoDB tables since MyISAM does not support foreign keys.
How do I fix "ERROR 1215: Cannot add foreign key constraint"?
ERROR 1215 occurs when MySQL cannot create a foreign key constraint due to mismatched data types, missing indexes, or storage engine issues. The fix involves verifying data types match exactly, ensuring the referenced column has an index, and confirming both tables use InnoDB.
What causes "ERROR 1215: Cannot add foreign key constraint"?
Data type mismatch between the foreign key column and the referenced primary/unique key column (e.g., INT vs. INT UNSIGNED, or different INTEGER sizes) Referenced column lacks a primary key or unique index required for the constraint Character set or collation differs between VARCHAR or TEXT columns in the foreign key relationship
Is the "ERROR 1215: Cannot add foreign key constraint" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading