Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Adding Foreign Key Fails in MySQL (errno 150)

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.

  1. Both tables are InnoDB.

  2. Both tables are UTF-8.

  3. 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??

like image 888
Raolin Avatar asked May 31 '13 21:05

Raolin


People also ask

How do I fix MySQL error 150?

Altering a table returns an error (errno: 150) if a foreign key definition is incorrectly formed for the altered table. Dropping an index required by a foreign key constraint. The foreign key constraint must be removed before dropping the index.

How do you fix a foreign key constraint failure?

The error message itself showing there is a foreign key constraint error, which means you are deleting a parent table where the child table contains the Primary table identifier as a foreign key. To avoid this error, you need to delete child table records first and after that the parent table record.

How to solve cannot add or update a child row a foreign key constraint fails?

Disabling the foreign key check The second way you can fix the ERROR 1452 issue is to disable the FOREIGN_KEY_CHECKS variable in your MySQL server. This variable causes MySQL to check any foreign key constraint added to your table(s) before inserting or updating.


2 Answers

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

like image 187
Kermit Avatar answered Nov 15 '22 20:11

Kermit


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.
like image 40
Ricardo Souza Avatar answered Nov 15 '22 19:11

Ricardo Souza