Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL refuses to create a foreign key (error 1215)

ErrorERROR 1215 (HY000): Cannot add foreign key constraint

Short answer

Error 1215 is a generic label. The real reason is in the LATEST FOREIGN KEY ERROR section of SHOW ENGINE INNODB STATUS. Most often the child and parent columns differ in type, sign, charset or collation, the parent column has no usable index, the engines differ, or SET NULL was used on a NOT NULL column.

  • Applies to MySQL 8.4, InnoDB
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Ask InnoDB why it rejected the last definition. The section LATEST FOREIGN KEY ERROR shows the statement and the constraint that failed.

    SHOW ENGINE INNODB STATUS\G
  2. 2Match what you see, then jump to its fix

    1. 1The child column is INT and the parent is BIGINT, or one is UNSIGNED and the other is notThe two columns do not have matching typesFix
    2. 2The key columns are CHAR or VARCHAR and the tables were created with different defaultsCharacter set or collation differs on string keysFix
    3. 3The parent column is not the first column of an index, for example it is the second column of a composite keyThe referenced column has no suitable indexFix
    4. 4One table is MyISAM, is TEMPORARY, or has user-defined partitionsDifferent engines, temporary tables or partitioningFix
    5. 5The child column is declared NOT NULL and the key uses SET NULLON DELETE SET NULL on a NOT NULL columnFix

What does the error look like?

  • ERROR 1215 (HY000): Cannot add foreign key constraint
  • ERROR 1005 (HY000): Can't create table (errno: 150 "Foreign key constraint is incorrectly formed")

Cause 1 of 5

The two columns do not have matching types

1Check

Compare the full column definitions on both sides. Size and sign must match for integer and decimal types.

SHOW CREATE TABLE parent\G
SHOW CREATE TABLE child\G

2Fix

Change the child column so it has exactly the parent's type and sign, then add the key.

ALTER TABLE child MODIFY parent_id BIGINT UNSIGNED NOT NULL;
ALTER TABLE child ADD CONSTRAINT fk_child_parent
  FOREIGN KEY (parent_id) REFERENCES parent (id);

3Verify

The constraint appears in the table definition.

SHOW CREATE TABLE child\G

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 5

Character set or collation differs on string keys

1Check

Read the CHARACTER SET and COLLATE of each key column. For text columns they must be identical; the length may differ.

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = DATABASE() AND column_name IN ('code');

2Fix

Convert the child column to the parent's character set and collation.

ALTER TABLE child MODIFY code VARCHAR(20)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;

3Verify

Add the foreign key again; the statement now succeeds.

ALTER TABLE child ADD CONSTRAINT fk_child_code
  FOREIGN KEY (code) REFERENCES parent (code);

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 5

The referenced column has no suitable index

1Check

List the indexes of the parent table and see whether the referenced column leads one of them.

SHOW INDEX FROM parent;

2Fix

Make the referenced column a primary key or unique key, or point the foreign key at the column that already leads an index.

ALTER TABLE parent ADD UNIQUE KEY uq_parent_code (code);

3Verify

Run the ALTER TABLE ... ADD FOREIGN KEY statement again.

SHOW INDEX FROM parent;

Confirmed by dev.mysql.com

Did this fix it?

Cause 4 of 5

Different engines, temporary tables or partitioning

1Check

Read the engine of both tables and check the table options for partitioning.

SELECT table_name, engine, create_options
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name IN ('parent','child');

2Fix

Move both tables to InnoDB before adding the key. A partitioned or temporary table cannot take part, so remove the partitioning or redesign.

ALTER TABLE parent ENGINE=InnoDB;
ALTER TABLE child ENGINE=InnoDB;

3Verify

Both tables now report InnoDB.

SHOW TABLE STATUS WHERE Name IN ('parent','child');

Confirmed by dev.mysql.com

Did this fix it?

Cause 5 of 5

ON DELETE SET NULL on a NOT NULL column

1Check

Look at the child column's nullability and the referential action in the failing statement.

SHOW CREATE TABLE child\G

2Fix

Either allow NULL in the child column or choose another action such as RESTRICT or CASCADE.

ALTER TABLE child MODIFY parent_id BIGINT UNSIGNED NULL;

3Verify

Add the constraint with ON DELETE SET NULL again.

ALTER TABLE child ADD CONSTRAINT fk_child_parent
  FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE SET NULL;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Copy the parent's column definition exactly when you write the child column, including UNSIGNED and collation.
  • Create parent tables before child tables in migrations.
  • Use one engine (InnoDB) and one default charset and collation for the whole schema.
  • Keep the output of SHOW ENGINE INNODB STATUS handy: it names the real cause after every failure.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.