Why Does MySQL Throw a 'Foreign Key Constraint is Incorrectly Formed' Error, and How Can I Fix It?

MySQL foreign key constraint error: type mismatch

In MySQL, when trying to establish a foreign key constraint between tables table1 and table2, the error "Foreign key constraint is incorrectly formatted" may occur. This constraint is designed to link the column IDFromTable1 in table2 to the column ID in table1 to ensure referential integrity.

Although both tables use the InnoDB storage engine and the data type of the columns is char type, the error still exists. The source of the problem is the difference in column properties.

Specifically, the foreign key column IDFromTable1 and the referenced column ID have different data types or lengths. To solve this problem, you must ensure that the corresponding columns participating in the foreign key relationship have exactly the same data type and length.

In this example, the foreign key column is defined as SMALLINT(5) UNSIGNED and the referenced column is INT(10) UNSIGNED. This type mismatch is resolved by aligning the column attributes, thereby successfully establishing the foreign key constraint.

The above is the detailed content of Why Does MySQL Throw a 'Foreign Key Constraint is Incorrectly Formed' Error, and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!