Search code examples
mysqlforeign-keysmysql-error-150

MySql Error 150 - Foreign keys

When I execute the follow two queries (I have stripped them down to absolutely necessary):

mysql> CREATE TABLE foo(id INT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE bar ( id INT, ref INT, FOREIGN KEY (ref) REFERENCES foo(id)) ENGINE InnoDB;

I get the following error: ERROR 1005 (HY000): Can't create table './test/bar.frm' (errno: 150)

Where the **** is my error? I haven't found him while staring at this for half an hour.

Solution

  • From FOREIGN KEY Constraints

    If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.

    My suspicion is that it's because you didn't create foo as InnoDB, as everything else looks OK.

    Edit: from the same page -

    Both tables must be InnoDB tables and they must not be TEMPORARY tables.