MySQL errno 150 without solution yet

Question

although this is a repeated question, I have been searching through most of the similar posts, but found nothing useful. Here's my SQL script for MySQL.

CREATE DATABASE IF NOT EXISTS store;
USE store;
CREATE TABLE IF NOT EXISTS Box (
coord VARCHAR (255),
box_id INT UNSIGNED NOT NULL,
img_path VARCHAR (256),
PRIMARY KEY (coord, box_id)
);
CREATE TABLE IF NOT EXISTS Tool (
serial VARCHAR (50),
tool_id INT,
descr VARCHAR (256),
box_id INT UNSIGNED NOT NULL,
tool_state BOOLEAN,
PRIMARY KEY (tool_id),
FOREIGN KEY (box_id) REFERENCES Box(box_id)
);

Output is: ERROR 1005 (HY000) at line 9: Can't create table 'store.Tool' (errno: 150) Any suggestion

In your first table Box primary key consists of 2 columns but in the second one you just use 1 column — StanislavL, May 06 '14 at 09:57
see http://stackoverflow.com/questions/953035/multiple-column-foreign-key-in-mysql — StanislavL, May 06 '14 at 09:58

score 1 · Accepted Answer · answered May 06 '14 at 09:57

From: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

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.

I guess you have to use the same amount of foreign key, in your code you use 2 PK in table Box, so either you use only box_id as your PK or add foreign key to table Tool..

Thanks for clearing my path. PK is composed by box_id, and coord now is left as UNIQUE field. Thank you — fasaas, May 06 '14 at 10:46

MySQL errno 150 without solution yet

1 Answers1