Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Adding a foreign key fails because the two columns do not match

ErrorERROR 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.

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

Fix it fast

  1. 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\G
  2. 2Match what you see, then jump to its fix

    1. 1One 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. 2Both 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

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\G

Confirmed by dev.mysql.com

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

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