mysql - Error Code: 1215. Cannot add foreign key constraint (foreign keys)

Question

CREATE DATABASE my_db;

CREATE TABLE class (
  classID int NOT NULL AUTO_INCREMENT,
  nameClass varchar(255),
  classLeader varchar(255),
  FOREIGN KEY (classLeader) REFERENCES student(studentID),
  PRIMARY KEY (classID));

CREATE TABLE student (
  studentID int NOT NULL AUTO_INCREMENT,
  lastName varchar(255),
  firstName varchar(255),
  classID int,
  FOREIGN KEY (classID) REFERENCES class(classID),
  PRIMARY KEY (studentID));

I am trying to ensure data consistency between the tables by using foreign key so that the DBMS can check for errors; however, it seems we can't do that for some reason. What's the error and is there an alternative? Also, when I fill a table that has a foreign key, I can't fill the field that's reserved for the foreign key(s), right? Also, is a foreign key considered to be a key at all?

question from:https://stackoverflow.com/questions/17691282/error-code-1215-cannot-add-foreign-key-constraint-foreign-keys

Answer 1

The most likely issue is this line:

FOREIGN KEY (classLeader) REFERENCES student(studentID),

The datatype of classLeader is VARCHAR(255). That has to match the datatype of the referenced column... student.studentID. And of course, the student table has to exist, and the studentID column has to exist, and the studentID column should be the PRIMARY KEY of the student table (although I believe MySQL allows this to be a UNIQUE KEY, rather than a PRIMARY KEY, or even just have an index on it.)

In any case, what's missing here is the output from SHOW CREATE TABLE student;

---

There's a datatype mismatch.

The classLeader VARCHAR(255) column cannot be a foreign key reference to studentID INT.

The datatypes of the two columns has to match.