MySQL Error 150 – Detailed Explanation and Solutions

foreign keyinnodbMySQL

When MySQL can't create a table due to a foreign key constraint problem, it returns the following error

Can't create table 'dbname.table_name' (errno: 150)

Is there anyway to have MySQL tell you (or to automatically lookup/analyze) why it couldn't create the table. For example, if you attempt to create a table with 10 foreign keys, and one key is invalid, is there a way to have MySQL tell you which one is the problematic key? Or do you need to rely on human intelligence and debugging to figure this out for yourself?

Best Answer

Unfortunately, I would have to go with

rely on human intelligence and debugging to figure this out for yourself

Here is why:

Suppose you have the following table 

CREATE TABLE projects (
    StaffID INT NOT NULL,
    ProjectName VARCHAR(20) NOT NULL,
    Allocation INT NOT NULL,
    PRIMARY KEY (StaffID, ProjectName) ,
    FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE
);

MySQL would have to look for a table called Staff. If it finds the table Staff, MySQL has to find a column in that table called StaffID.

For a table with 10 foreign keys, this algorithm would be followed 10 times. Sadly, MySQL does not generate a error stating which table or column name caused the CREATE TABLE to fail. You could just remove one of the foreign keys and try it with 9, then 8, and so on.

Here is a strange case that require intelligence:

  • What if you did a mysqldump of InnoDB data from a MySQL Databases running in Windows and try to import it into Linux ?
  • What if you did a mysqldump of InnoDB data from a MySQL Databases running in Linux and try to import it into Windows ?

There is a case sensitivity issue. The foreign key references may fail due to this.

I have discussed this back in March 2014 : creating table result in errno 150

Related Question