Why Am I Getting MySQL Foreign Key Error 1215: \'Cannot add foreign key constraint\'?

Troubleshooting MySQL Foreign Key Error: 1215

When attempting to add a foreign key constraint in MySQL, you may encounter Error Code: 1215 indicating "Cannot add foreign key constraint (foreign keys)." This error typically occurs due to data type mismatch or structural issues.

Data Type Mismatch

One possible cause is a mismatch in data types between the column referencing the foreign key and the referenced column. For instance, if the classLeader column in the class table has a VARCHAR(255) data type while the referenced studentID column in the student table is an INT, the foreign key constraint will fail. The data types of both columns must be identical.

Structural Issues

If data types match, the issue may lie in structural discrepancies between the tables. Notably:

• The referenced table (student in this case) must exist.
• The referenced column (studentID) must exist within the referenced table.
• The referenced column (studentID) should be the primary key or unique key of the referenced table (though in MySQL, an index may suffice).

Example

-- Create student table
CREATE TABLE student (
  studentID int NOT NULL AUTO_INCREMENT,
  lastName varchar(255),
  firstName varchar(255),
  PRIMARY KEY (studentID)
);

-- Create class table
CREATE TABLE class (
  classID int NOT NULL AUTO_INCREMENT,
  nameClass varchar(255),
  classLeader int,  -- Change to INT to match studentID type
  FOREIGN KEY (classLeader) REFERENCES student(studentID),
  PRIMARY KEY (classID)
);

Foreign Keys vs. Keys

Foreign keys are not considered traditional keys (such as primary or unique keys) that uniquely identify rows within a table. Instead, their primary purpose is to ensure referential integrity and maintain relationships between tables. By enforcing foreign keys, you can prevent inconsistencies and orphaned data.

The above is the detailed content of Why Am I Getting MySQL Foreign Key Error 1215: \'Cannot add foreign key constraint\'?. For more information, please follow other related articles on the PHP Chinese website!