MySQL error 3780, identified by the symbol ER_FK_INCOMPATIBLE_COLUMNS, is raised when you attempt to create or alter a table with a foreign key constraint where the referencing column and the referenced column have incompatible data types. The error message reads:
ERROR 3780 (HY000): Referencing column '<referencing_col>' and referenced column '<referenced_col>' in foreign key constraint '<constraint_name>' are incompatible.
The SQLSTATE code is HY000 (general error). MySQL requires that foreign key columns match not just in base type but also in length, character set, collation, and signedness. Any mismatch in these attributes causes this error.
Impact
This error is a DDL-level failure — it occurs at CREATE TABLE or ALTER TABLE time and prevents the foreign key constraint from being added. No data is lost or corrupted; the statement simply does not execute. However, the impact on development and deployment workflows can be significant:
- Schema migrations fail midway, leaving tables in an intermediate state if a multi-statement migration is not wrapped in a transaction.
- Application bootstrapping fails when ORMs (such as Hibernate, Sequelize, or SQLAlchemy) attempt to create or validate schema on startup.
- Downstream tables that depend on the problematic table cannot be created until the parent table's constraint issue is resolved.
Because the constraint is rejected at definition time, no existing data is affected. The fix is purely a schema change.
Common Causes
Integer type or signedness mismatch
The most frequent cause is a mismatch in integer type or signedness between the foreign key column and the primary key it references. MySQL treats INT and INT UNSIGNED as incompatible, as are BIGINT and INT, even though one can hold a superset of the other's values.
-- parent table uses UNSIGNED
CREATE TABLE orders (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
);
-- child table incorrectly uses signed INT
CREATE TABLE order_items (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL, -- missing UNSIGNED
FOREIGN KEY (order_id) REFERENCES orders(id)
);
-- ERROR 3780: incompatible columns
String type, length, or collation mismatch
Foreign keys on string columns require the same character set and collation, and often the same declared length. A common case is referencing a VARCHAR(100) column with a VARCHAR(50) column, or mixing utf8mb4 with latin1.
CREATE TABLE categories (
code VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL PRIMARY KEY
);
CREATE TABLE products (
category_code VARCHAR(20) CHARACTER SET latin1 NOT NULL,
FOREIGN KEY (category_code) REFERENCES categories(code)
);
-- ERROR 3780: incompatible columns (charset mismatch)
ENUM or SET column mismatch
Referencing an ENUM or SET column with another ENUM or SET that has a different list of values is not allowed. MySQL considers these incompatible even if the value sets overlap.
Using generated columns or expression-based columns
Attempting to create a foreign key on a virtual generated column or using a generated column as the target of a foreign key reference will raise this error in many MySQL versions, because generated columns have constraints on how they can participate in constraints.
Type category mismatch
Completely different type categories — for example, referencing an INT column with a VARCHAR column, or a DATE column with a TIMESTAMP column — are always incompatible for foreign key purposes.
Troubleshooting and Resolution Steps
Step 1: Identify the conflicting columns
Use SHOW CREATE TABLE on both the parent and child tables to compare the column definitions precisely:
SHOW CREATE TABLE parent_table\G
SHOW CREATE TABLE child_table\G
Pay close attention to:
- Base data type (
INT,BIGINT,VARCHAR, etc.) - Length or precision (
INT(11)vsBIGINT,VARCHAR(50)vsVARCHAR(100)) - Signedness (
UNSIGNEDmodifier) - Character set and collation for string types
NULLvsNOT NULL(while not always the direct cause of 3780, mismatches here can mask other issues)
You can also query information_schema for precise column metadata:
SELECT
TABLE_NAME,
COLUMN_NAME,
COLUMN_TYPE,
CHARACTER_SET_NAME,
COLLATION_NAME,
IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_NAME IN ('parent_table', 'child_table')
AND COLUMN_NAME IN ('referenced_col', 'referencing_col');
Step 2: Align column types exactly
Once you have identified the discrepancy, alter the child column (or in some cases the parent) to match exactly. The most common fix is aligning the child to match the parent's primary key definition.
Fix integer signedness:
ALTER TABLE order_items
MODIFY COLUMN order_id INT UNSIGNED NOT NULL;
Fix string character set or collation:
ALTER TABLE products
MODIFY COLUMN category_code VARCHAR(20)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
NOT NULL;
Fix integer type size:
-- If parent uses BIGINT, child must too
ALTER TABLE child_table
MODIFY COLUMN parent_id BIGINT UNSIGNED NOT NULL;
Step 3: Re-add the foreign key constraint
After aligning the column types, add the foreign key:
ALTER TABLE order_items
ADD CONSTRAINT fk_order_items_orders
FOREIGN KEY (order_id) REFERENCES orders(id);
Step 4: Verify with SHOW CREATE TABLE
Confirm the constraint was created:
SHOW CREATE TABLE order_items\G
The output should include the CONSTRAINT clause for the foreign key.
Handling ORM-generated schemas
If your schema is generated by an ORM, the mismatch often originates from model definitions. In Hibernate (Java), ensure both the @ManyToOne referencing column and the @Id on the referenced entity use the same Java type and that the column-level DDL annotations specify the same database type. In Sequelize (Node.js) or SQLAlchemy (Python), ensure foreign key column definitions explicitly set the type to match the referenced primary key — do not rely on implicit type inference.
Additional Information
MySQL's foreign key type-compatibility rules are stricter than the rules for implicit casting at query time. Even when MySQL would silently cast between two types in a WHERE clause, it will refuse to create a foreign key between columns of those same types. This is by design: consistent types on both ends of a foreign key guarantee that index lookups remain efficient and that referential integrity checks can rely on direct value comparison without conversion.
The UNSIGNED attribute deserves special attention. Because AUTO_INCREMENT primary keys defined with INT or BIGINT are commonly declared as UNSIGNED to double the positive range available, the referencing foreign key column must also be UNSIGNED. Missing this is the single most common cause of error 3780 in production codebases.
In MySQL 8.0, display width for integer types (e.g., INT(11)) is deprecated and ignored for storage purposes, but it does not affect the signedness requirement. Two columns can have different display widths but must still agree on signedness and base type.
When using utf8mb4 as your default character set (which is recommended for full Unicode support including emoji), set it at the database level so that all new tables and columns inherit it by default, reducing the risk of collation mismatches:
ALTER DATABASE your_database
CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;
Frequently Asked Questions
Does error 3780 cause data loss? No. The error occurs during DDL execution and simply prevents the constraint from being created. Existing table data is not modified.
Can I use SET foreign_key_checks = 0 to bypass this error?
No. Disabling foreign_key_checks suppresses enforcement of referential integrity at DML time (inserts, updates, deletes), but it does not override the type-compatibility validation that produces error 3780. The column types must be compatible regardless of this session variable.
Why does MySQL require exact type matching instead of allowing compatible types? MySQL uses the foreign key column's index for the referential integrity lookup. If the types differ, MySQL cannot use the index directly and would need to perform implicit casts on every check, making constraint enforcement expensive and potentially unreliable. Requiring exact type compatibility keeps referential checks fast and unambiguous.
I changed the column type but the error persists. What should I check?
Run SHOW CREATE TABLE again and verify the change took effect. Also confirm that the character set and collation match, not just the base type — this is a frequent secondary cause that is easy to overlook. Check for the UNSIGNED keyword on both columns if you are using integer types.
What if I cannot change the parent table's column type? If the referenced table is shared or locked by existing constraints, align the child column to the parent rather than the reverse. It is almost always safer to change the referencing (child) column to match the referenced (parent) column than to change a primary key type on a table that may already have dependents.
