Errno 150 can't create the table MYSQL

Question

Im trying to create a table with 2 foreign keys from 2 other tables but I always get an errno 150, i don't understand why they all have the same data type and I am using InnoDB, what could be wrong ? Here is my code

    CREATE TABLE Employees (
    empID INT NOT NULL AUTO_INCREMENT,
    empSurname VARCHAR(255) NOT NULL,
    empLastname VARCHAR(255) NOT NULL,
    empJobtitle VARCHAR(255) NOT NULL,
    empLinemanager VARCHAR(255) NOT NULL,
    CONSTRAINT pk_employees PRIMARY KEY (empID)
) ENGINE=InnoDB;

CREATE TABLE Skills (
    sklID INT NOT NULL AUTO_INCREMENT,
    sklName VARCHAR(255) NOT NULL,
    CONSTRAINT pk_skills PRIMARY KEY (sklID)
) ENGINE = InnoDB;

CREATE TABLE Employees_Skills (
    empskID INT NOT NULL AUTO_INCREMENT,
    empskLevel INT NOT NULL,
    sklID INT NOT NULL,
    empID INT NOT NULL,
    CONSTRAINT fk_employees_skills FOREIGN KEY (sklID) REFERENCES Skills(sklID),
    CONSTRAINT fk_employees_skills_1 FOREIGN KEY (empID)  REFERENCES Employees(empID),
    CONSTRAINT pk_employees_skills PRIMARY KEY (empskID)
) ENGINE = InnoDB;   

CREATE TABLE Project (
    projID INT NOT NULL AUTO_INCREMENT,
    projName VARCHAR(255) NOT NULL,
    projDuration INT NOT NULL,
    projStartdate VARCHAR (255) NOT NULL,
    CONSTRAINT pk_project PRIMARY KEY (projID)
) ENGINE = InnoDB;

CREATE TABLE Project_Staff (
    empID INT NOT NULL,
    projID INT NOT NULL,
    CONSTRAINT fk_project_staff1 FOREIGN KEY (empID) REFERENCES  Employees(empID),
    CONSTRAINT fk_project_staff2 FOREIGN KEY (projID) REFERENCES Project(projID)
) ENGINE = InnoDB;

CREATE TABLE Skill_For_Project (
    sklreqDuration INT NOT NULL,
    projID INT NOT NULL,
    sklID INT NOT NULL,
    CONSTRAINT fk_skill_for_project1 FOREIGN KEY (sklID) REFERENCES  Skills(empID),
    CONSTRAINT fk_skill_for_project2 FOREIGN KEY (projID) REFERENCES Project(projID)
) ENGINE = InnoDB;

Please run `show engine innodb status` to find the exact error message. — Álvaro González, Mar 17 '14 at 09:46
I see that "learn to fish" comments are not as popular as "here you are your dinner" ones. I'll elaborate mine as answer so at least it'll help others in the future. — Álvaro González, Mar 17 '14 at 10:01

score 3 · Answer 1 · answered Mar 17 '14 at 09:48

your problem is here

   Skills(empID)

you dont have empID in skills table change it to

  Skills(sklID)

here

CREATE TABLE Skill_For_Project (
sklreqDuration INT NOT NULL,
projID INT NOT NULL,
sklID INT NOT NULL,
CONSTRAINT fk_skill_for_project1 FOREIGN KEY (sklID) REFERENCES  Skills(sklID),
CONSTRAINT fk_skill_for_project2 FOREIGN KEY (projID) REFERENCES Project(projID)
) ENGINE = InnoDB;

here working demo http://sqlfiddle.com/#!2/561cf

score 0 · Answer 2 · answered Mar 17 '14 at 10:00

If you don't want to ask in a forum every time you get a foreign key error, you can fetch the exact error message with this query:

show engine innodb status

In your case, the output contains this:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
140317 10:58:46 Error in foreign key constraint of table test/skill_for_project:
 FOREIGN KEY (sklID) REFERENCES  Skills(empID),
    CONSTRAINT fk_skill_for_project2 FOREIGN KEY (projID) REFERENCES Project(projID)
) ENGINE = InnoDB:
Cannot resolve column name close to:
),
    CONSTRAINT fk_skill_for_project2 FOREIGN KEY (projID) REFERENCES Project(projID)
) ENGINE = InnoDB

... which is basically what's already been pointed out by other users: the column does not exist ("Cannot resolve column name").

You need a user with the PROCESS privilege, which shouldn't be an issue in your local development box.

Errno 150 can't create the table MYSQL

2 Answers2