Adding a foreign key fails because the two columns do not match
ERROR 3780 (HY000): Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'fk_orders_user' are incompatible.Short answer
Error 3780 means the column in the child table and the column it references are defined differently. For integers the size and the signed or unsigned setting must be identical; for text columns the character set and collation must be identical. Compare both definitions, then alter the child column to match the parent.
Fix it fast
1Find out which cause you have
Print the definition of both tables and compare the two columns named in the error message, character by character.
SHOW CREATE TABLE users\G SHOW CREATE TABLE orders\G2Match what you see, then jump to its fix
- 1
One table shows `id` int unsigned or bigint, the other shows plain int.Integer size or sign differs (INT against BIGINT, signed against UNSIGNED)Fix - 2
Both columns are VARCHAR or CHAR, but one table was created as utf8mb4 and the other as latin1, or the collations differ.String columns use a different character set or collationFix
- 1
What does the error look like?
ERROR 3780 (HY000): Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'fk_orders_user' are incompatible.
Cause 1 of 2
Integer size or sign differs (INT against BIGINT, signed against UNSIGNED)
1Check
Read the data type of both columns from the information schema. The manual requires that the size and sign of fixed precision types such as INTEGER and DECIMAL are the same.
SELECT table_name, column_name, column_type
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND ((table_name = 'users' AND column_name = 'id')
OR (table_name = 'orders' AND column_name = 'user_id'));2Fix
Change the child column to the exact type of the parent. MODIFY replaces the whole definition, so restate NOT NULL and any default you want to keep.
ALTER TABLE orders MODIFY user_id BIGINT UNSIGNED NOT NULL;
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id);3Verify
The ALTER TABLE for the constraint now succeeds, and the constraint shows in the table definition.
SHOW CREATE TABLE orders\GConfirmed by dev.mysql.com
Cause 2 of 2
String columns use a different character set or collation
1Check
For character string columns the character set and collation must match; the length does not have to. Read both from the information schema.
SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND ((table_name = 'users' AND column_name = 'code')
OR (table_name = 'orders' AND column_name = 'user_code'));2Fix
Convert the child column to the parent's character set and collation. Use MODIFY on the single column rather than converting the whole table, and repeat the original length and NOT NULL.
ALTER TABLE orders
MODIFY user_code VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;3Verify
Add the foreign key again.
ALTER TABLE orders
ADD CONSTRAINT fk_orders_code FOREIGN KEY (user_code) REFERENCES users (code);Confirmed by dev.mysql.com
How do I stop it coming back?
- Copy the parent's exact column type when you write the child column, including UNSIGNED.
- Set one default character set and collation per database so new tables start identical.
- Check column types before writing a migration that adds a foreign key, not after it fails in production.
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
MySQL refuses to create a foreign key (error 1215)
ERROR 1005 (HY000): Can't create table (errno: 150 "Foreign key constraint is incorrectly formed")
Error 1215 is a generic label.
5 causes, each with a fixDatabases
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 fix