MySQL refuses to create a foreign key (error 1215)
ERROR 1215 (HY000): Cannot add foreign key constraintShort 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.
Fix it fast
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\G2Match what you see, then jump to its fix
- 1
The 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
The key columns are CHAR or VARCHAR and the tables were created with different defaultsCharacter set or collation differs on string keysFix - 3
The 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
One table is MyISAM, is TEMPORARY, or has user-defined partitionsDifferent engines, temporary tables or partitioningFix - 5
The child column is declared NOT NULL and the key uses SET NULLON DELETE SET NULL on a NOT NULL columnFix
- 1
What does the error look like?
ERROR 1215 (HY000): Cannot add foreign key constraintERROR 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\G2Fix
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\GConfirmed by dev.mysql.com
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
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
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
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\G2Fix
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix