Why Am I Getting MySQL Error 150: Foreign Key Constraint Failure?

MySQL Errno 150: Foreign Key Constraint Failure

When attempting to create a foreign key constraint, users may encounter Errorno 150. A foreign key constraint ensures the integrity of data by referencing a field in another table, forcing the referenced field to exist in the source table.

Potential Cause: Data Type Discrepancy

One common cause of Errorno 150 is a discrepancy in data types between the referenced column in the child table (Sections) and the primary key column in the referenced table (Instructors).

Example:

create TABLE Instructors (
  ...
  ID int(10),
  ...
);

create table Sections (
  ...
  Instructor_ID varchar(10),
  ...
  FOREIGN KEY (Instructor_ID) REFERENCES Instructors(ID)
  ...
);

In this example, the ID column in the Instructors table is defined as an integer, while the Instructor_ID column in the Sections table is defined as a string. This difference in data types prevents the foreign key constraint from being established.

Solution:

To resolve this issue, ensure that the data types of the referenced columns in both tables match. In the above example, change Instructor_ID to be an integer data type:

create table Sections (
  ...
  Instructor_ID int(10),
  ...
);

Other Potential Causes:

• Primary Key Not Defined: The referenced table (Instructors) may not have a primary key defined.
• Data Integrity Violation: An existing row in the child table (Sections) may contain a foreign key value that does not exist in the referenced table (Instructors).
• Incorrect Syntax: Ensure that the syntax for the FOREIGN KEY constraint is correct, including the ON DELETE and ON UPDATE clauses.

The above is the detailed content of Why Am I Getting MySQL Error 150: Foreign Key Constraint Failure?. For more information, please follow other related articles on the PHP Chinese website!