Adding Foreign Key Fails in MySQL (errno 150)

Question

I've read many other posts about receiving MySQL errno 150 when trying to add a foreign key copnstraint, however I haven't found a solution yet. I hope I'm not doing something stupid. I made a simple test case.

Both tables are InnoDB.
Both tables are UTF-8.
~~Both columns are int(11) unsigned (making color_id NOT NULL doesn't make a difference).~~ (EDIT: I WAS WRONG ABOUT THIS, AND THIS WAS THE SOLUTION)

Here are my two tables:

Table widgets:

CREATE TABLE `widgets` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL DEFAULT '',
`color_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Table colors:

CREATE TABLE `colors` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I just created these tables, there is no content. When I try to add a foreign key constraint to link widgets.color_id to colors.id, this happens:

mysql> ALTER TABLE `widgets` ADD FOREIGN KEY (`color_id`) REFERENCES `color` (`id`);

ERROR 1005 (HY000): Can't create table 'production.#sql-7b1_2dd7' (errno: 150)

I'll just add that I haven't been able to use my GUI tool of choice -- Sequel Pro on OSX -- either. I get the same error message when trying to create a foreign key relationship.

SHOW ENGINE INNODB STATUS returns this:

130531 17:23:06 Error in foreign key constraint of table production/#sql-7b1_2c80: 
FOREIGN KEY (`color_id`) REFERENCES `colors` (`id`): Cannot find an index in 
the referenced table where the referenced columns appear as the first columns, 
or column types in the table and the referenced table do not match for constraint.

Am I doing something ridiculously stupid??

Kermit · Accepted Answer · 2013-05-31 21:34:33Z

30

color_id is not unsigned and has a DEFAULT NULL. The column types must be identical.

answered May 31, 2013 at 21:34

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Raolin Over a year ago

Ok, that answers that question--yes, I was ridiculously stupid. I swear I checked the unsigned even while I was posting the question, but I obviously didn't. How embarrassing. Thank you.

Kermit Over a year ago

@Raolin It happens to the best of us. At least your question was well written.

NaturalBornCamper Over a year ago

Thank ya! I was about to post on stackoverflow until I found out that my second column was not unisgned!

Kermit Over a year ago

@NaturalBornCamper Glad to hear and good job on finding your mistake.

daleyjem Over a year ago

Allowing NULL doesn't have be changed in the child table though, supposing you don't want to delete the matching row(s). A typical example could cascade on update, but set NULL on delete... to preserve the matching row(s).

|

Ricardo Souza · Accepted Answer · 2013-05-31 21:37:17Z

7

Its almost always a case of type mismatch between the primary key and the foreign key.

Some tips to help in most cases:

Check to see if the table you area trying to relate uses an engine that supports foreing keys like InnoDB
Check if the columns are identically typed and nulled. eg (the 2 columns MUST match type, type size and sign)
Check if the destination column have an index or primary key.

answered May 31, 2013 at 21:37

Ricardo Souza

16.5k6 gold badges40 silver badges70 bronze badges

1 Comment

Naveed Abbas Over a year ago

ALMOST ALWAYS

Collectives™ on Stack Overflow

Adding Foreign Key Fails in MySQL (errno 150)

2 Answers 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related