MySQL error 3780: Foreign key columns are incompatible
The referencing and referenced columns of a foreign key have different data types, lengths, signedness or collations.
- Error number
3780- Name
ER_FK_INCOMPATIBLE_COLUMNS- SQLSTATE
HY000- Category
- Keys and constraints
What it means
InnoDB requires the two sides of a foreign key to be compatible: same integer type and signedness, compatible string types with the same character set and collation. 3780 names both columns and the constraint, which makes it much easier to fix than the older 1215.
Common causes
INTon one side andBIGINTorINT UNSIGNEDon the other.VARCHARcolumns with different character sets or collations, for example after converting only one table toutf8mb4.
How to fix it
-
Compare the two columns.
SELECT table_name, column_name, column_type, character_set_name, collation_name FROM information_schema.columns WHERE table_schema = DATABASE() AND ((table_name = 'orders' AND column_name = 'customer_id') OR (table_name = 'customers' AND column_name = 'id'));
-
Change the child column to match the parent, then create the foreign key.
ALTER TABLE orders MODIFY customer_id BIGINT UNSIGNED NOT NULL;
Related errors
More keys and constraints errors
1062Duplicate entry for key1061Duplicate key name1452Foreign key fails: cannot add child row1451Foreign key fails: cannot delete parent row1216Cannot add or update a child row1217Cannot delete or update a parent row1824Failed to open the referenced table1048Column cannot be null
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.