Why Does MySQL Throw a 'Foreign key constraint is incorrectly formed' Error?

Troubleshooting MySQL's Foreign Key Constraint Errors

Creating a foreign key relationship in MySQL requires careful attention to data type compatibility. Mismatched data types or lengths between the foreign key column and the referenced primary key column will result in a "Foreign key constraint is incorrectly formed" error.

Here's a common scenario:

• Table1: Contains an ID column (primary key) with a CHAR data type.
• Table2: Contains an IDFromTable1 column referencing Table1's ID column.

Attempting to create the foreign key constraint using this query:

<code class="language-sql">ALTER TABLE `table2`  
ADD CONSTRAINT `FK1` 
FOREIGN KEY (`IDFromTable1`) REFERENCES `table1` (`ID`) 
ON UPDATE CASCADE 
ON DELETE CASCADE;</code>

will fail if IDFromTable1 and ID have different data types or lengths. For example, if IDFromTable1 is VARCHAR(50) and ID is CHAR(10), the constraint will not be created.

Solution:

The solution is straightforward: ensure the foreign key column and the referenced column share the exact same data type and length. Adjusting the column definitions to match will resolve the error and allow the foreign key relationship to be established successfully.

The above is the detailed content of Why Does MySQL Throw a 'Foreign key constraint is incorrectly formed' Error?. For more information, please follow other related articles on the PHP Chinese website!