// MySQL error reference

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
Error message
Referencing column '%s' and referenced column '%s' in foreign key constraint '%s' are incompatible.

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

  • INT on one side and BIGINT or INT UNSIGNED on the other.
  • VARCHAR columns with different character sets or collations, for example after converting only one table to utf8mb4.

How to fix it

  1. 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'));
  2. 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

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.

// fewer errors, faster fixes

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.