MySQL Errno 150: Why Can\'t I Create My Foreign Key Constraint?

Troubleshooting MySQL Errno 150: Foreign Key Error

When creating a foreign key constraint in MySQL, you may encounter the infamous "Errno 150" error. This article aims to pinpoint the root cause of this error and provide a solution.

The error message typically indicates "Can't create table" due to an invalid reference in a foreign key declaration. This can arise when there is a mismatch between the data types of the columns being referenced.

In the given script, a Sections table is defined with foreign keys referencing the Courses and Instructors tables. Careful examination reveals that the data types in the referenced columns match: varchar(10) for Course_Code in both tables and varchar(10) for ID in both tables.

However, there is a crucial detail that can easily be overlooked: the underlying data types. While the varchar data type may appear identical, it allows for varying character lengths. This means that the ID column in the Instructors table may store values that have a different character count than those in the Course_Code column in the Courses table.

To resolve the Errno 150 error, ensure that the referenced columns have identical underlying data types, including character length and any special attributes (e.g., unsigned or zerofill). By ensuring precise data type matching, you can establish valid foreign key relationships and resolve this common MySQL error.

The above is the detailed content of MySQL Errno 150: Why Can\'t I Create My Foreign Key Constraint?. For more information, please follow other related articles on the PHP Chinese website!