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

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

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

If the Engine column shows MyISAM, convert the table:

ALTER TABLE child_table ENGINE=InnoDB;
ALTER TABLE parent_table ENGINE=InnoDB;

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

SHOW CREATE TABLE parent_table;
DESCRIBE parent_table;

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

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

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:

ALTER TABLE child_table MODIFY COLUMN child_id INT UNSIGNED;

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

SHOW INDEX FROM parent_table;

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

ALTER TABLE parent_table ADD UNIQUE KEY uk_parent_id (parent_id);

Or if it should be the primary key:

ALTER TABLE parent_table ADD PRIMARY KEY (parent_id);

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

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:

ALTER TABLE child_table MODIFY COLUMN child_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

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

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.

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

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:

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
);