MySQL Error 3780: Incompatible Foreign Key Columns

MySQL Error 3780 (HY000, ER_FK_INCOMPATIBLE_COLUMNS) means the child foreign-key column and the referenced parent column have incompatible definitions. The full message names both columns and the constraint. It occurs while MySQL creates or alters the foreign-key definition, not when an INSERT supplies a missing parent value. See the MySQL 8.4 error reference and the foreign-key column rules.

Example: signedness and integer width differ

Here, invoices.account_id is INT, but it references accounts.account_id, which is BIGINT UNSIGNED. MySQL rejects the foreign key because the corresponding integer definitions do not match:

CREATE TABLE accounts (
  account_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (account_id)
) ENGINE = InnoDB;

CREATE TABLE invoices (
  invoice_id BIGINT UNSIGNED NOT NULL,
  account_id INT NOT NULL,
  PRIMARY KEY (invoice_id),
  KEY ix_invoices_account_id (account_id),
  CONSTRAINT fk_invoices_account
    FOREIGN KEY (account_id)
    REFERENCES accounts (account_id)
) ENGINE = InnoDB;

Make the child column compatible with the parent before adding the constraint. For this example, its definition would be account_id BIGINT UNSIGNED NOT NULL. If the child table already contains data, check that existing values are nonnegative and fit the target type before changing the column. Do not change signedness or width blindly on a production table.

Compare the actual column definitions

Start with SHOW CREATE TABLE for both tables. This shows the definitions MySQL is using, including column types, character sets, collations, engines, and indexes:

SHOW CREATE TABLE accounts\G
SHOW CREATE TABLE invoices\G

You can also query the column metadata directly:

SELECT TABLE_NAME,
       COLUMN_NAME,
       COLUMN_TYPE,
       CHARACTER_SET_NAME,
       COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND ((TABLE_NAME = 'accounts' AND COLUMN_NAME = 'account_id')
    OR (TABLE_NAME = 'invoices' AND COLUMN_NAME = 'account_id'));

Check these properties for each corresponding pair:

1. Integer and decimal definitions: fixed-precision types need the same size and sign. For integers, compare the type width and UNSIGNED attribute. For DECIMAL, compare precision, scale, and signedness.
2. Character columns: nonbinary string columns need the same character set and collation. Their declared lengths do not have to be identical, so changing VARCHAR lengths alone may not fix the error.
3. The pair of columns: confirm the child column references the intended parent column. For a multi-column foreign key, compare each child and parent column in order.

If the mismatch is a character set or collation, alter only the intended column after checking the data and application expectations. Changing a table’s default character set does not necessarily change columns that already have explicit definitions.

Add the constraint after aligning the columns

After checking the existing values, change the child column to the matching definition and add the foreign key. This example assumes the referenced accounts.account_id is indexed:

ALTER TABLE invoices
  MODIFY account_id BIGINT UNSIGNED NOT NULL;

ALTER TABLE invoices
  ADD CONSTRAINT fk_invoices_account
  FOREIGN KEY (account_id)
  REFERENCES accounts (account_id);

If the column types now match but MySQL reports Error 1822 about a missing index in the referenced table, see how to fix MySQL Error 1822 for parent-key index checks. For a generic foreign-key definition error, see the Error 1215 troubleshooting guide.

Error 3780 is different from Error 1452: Error 1452 means a child row contains a key with no matching parent row. If an older MySQL version reports only the generic Error 1215, use the Error 1215 troubleshooting guide to inspect the full table definitions and InnoDB diagnostic.