18

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Martin Thurau
  • 7,564
  • 7
  • 43
  • 80

7 Answers7

31

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.

Greg
  • 316,276
  • 54
  • 369
  • 333
  • A bit of quick testing, creating a `foo` as above with MyISAM, and a `bar` with InnoDB, suggests that that's the case - try double-checking your table type for `foo`. – Rob May 05 '09 at 15:26
  • 1
    Even when both tables were InnoDB, I still had the same problem. I fixed it by created the index on the referred table before creating the foreign key. – IROEGBU Sep 04 '13 at 17:29
  • It doesn't help that MySQL Workbench 6.3 seems to randomly change the engine type to MyISAM from InnoDB when I'm creating new tables. – Chris Peacock Oct 15 '16 at 22:55
12

You can use the command SHOW ENGINE INNODB STATUS to get more specific information about the error.

It will give you a result with a Status column containing a lot of text.

Look for the section called LATEST FOREIGN KEY ERROR which could for example look like this:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
190215 11:51:26 Error in foreign key constraint of table `mydb1`.`contacts`:
Create  table `mydb1`.`contacts` with foreign key constraint failed. You have defined a SET NULL condition but column 'domain_id' is defined as NOT NULL in ' FOREIGN KEY (domain_id) REFERENCES domains (id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT contacts_teams_id_fk FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT' near ' ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT contacts_teams_id_fk FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT'.
Tommy Bravo
  • 532
  • 8
  • 29
user2906840
  • 121
  • 1
  • 2
  • Agree! It shows a section "Last foreign key error", which in my case told me the target field name of a foreign key constraint was not found. This is more helpful than a sheer "Error 150" ;-) – Marek Stanley Jun 02 '14 at 17:24
  • In my case I had forgotten to set the column to allow NULL, and put SET NULL on the constraint. But I only found out the reason by running `SHOW ENGINE INNODB STATUS` – Robert K Apr 13 '17 at 14:21
  • This should be the top answer! Although it could help to know there is a LATEST FOREIGN KEY ERROR section in the 'Status' column output for this query. As @MarekStanley also states clearly. – Tommy Bravo Feb 15 '19 at 10:56
4

To create a foreign key ,

  1. both the main column and the reference column must have same definition.
  2. both tables engine must be InnoDB.

You can alter the engine of table using this command , please take the backup before executing this command.

alter table [table name] ENGINE=InnoDB;

Suyash Jain
  • 561
  • 7
  • 18
  • 3
    "must have same definition" was same charset encoding for me – gengisdave Oct 19 '15 at 09:16
  • "must have same definition" was same same column flags for me: The existing primary key I was referencing was UNSIGNED, so I had to make my new table's column that also to foreign key on the first – cellepo Jun 24 '16 at 22:33
1

I had the same problem, for those who are having this also:

check the table name of the referenced table

I had forgotten the 's' at the end of my table name

eg table Client --> Clients

:)

webmaster
  • 1,960
  • 24
  • 29
1

Apart form many other reasons to end up with MySql Error 150 (while using InnoDB), One of the probable reason, is the undefined KEY in the create statement of the table containing the column name referenced as a foreign key in the relative table.

Let's say the create statement of master table is -

CREATE TABLE 'master_table' (
 'id' int(10) NOT NULL AUTO_INCREMENT,
 'record_id' char(10) NOT NULL,
 'name' varchar(50) NOT NULL DEFAULT '',
 'address' varchar(200) NOT NULL DEFAULT '',
 PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and the create syntax for the relative_table table where the foreign key constraint is set from primary table -

CREATE TABLE 'relative_table' (
 'id' int(10) NOT NULL AUTO_INCREMENT,
 'salary' int(10) NOT NULL DEFAULT '',
 'grade' char(2) NOT NULL DEFAULT '',
 'record_id' char(10) DEFAULT NULL,
 PRIMARY KEY ('id'),
 CONSTRAINT 'fk_slave_master' FOREIGN KEY ('record_id') REFERENCES 'master' ('record_id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This script is definitely going to end with MySql Error 150 if using InnoDB.

To solve this, we need to add a KEY for the The column record_id in the master_table table and then reference in the relative_table table to be used as a foreign_key.

Finally, the create statement for the master_table, will be -

CREATE TABLE 'master_table' (
 'id' int(10) NOT NULL AUTO_INCREMENT,
 'record_id' char(10) NOT NULL,
 'name' varchar(50) NOT NULL DEFAULT '',
 'address' varchar(200) NOT NULL DEFAULT '',
 PRIMARY KEY ('id'),
 KEY 'record_id' ('record_id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Madhab Chandra Pal
  • 11
  • 1
0

I had very same problem and the reason was the "collation" of columns was different. One was latin1 while the other was utf8

Hamed J.I
  • 21
  • 2
0

This may also happen if you have not given correct column name after "references" keyword.

Ajay Sharma
  • 846
  • 10
  • 21