Unable to create table (errno: 150). InnoDB Adds Foreign Key Constraints

Question

Actually I hate to use other people's time, but it seems that the problem just does not disappear.

I reviewed all the recommendations at http://verysimple.com/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/ and http: //forums.mysql. com / read.php? 22.19755.191975 # msg-19755 but nothing.

hope someone points out a dumb mistake.

Here are the tables:

CREATE TABLE IF NOT EXISTS `shop`.`category` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `category_id` INT(11) NOT NULL , `parent_id` INT(11) NULL DEFAULT '0' , `lang_id` INT(11) NOT NULL , ...other columns... PRIMARY KEY (`id`, `category_id`) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci; CREATE TABLE IF NOT EXISTS `shop`.`product_category` ( `category_id` INT(11) NOT NULL , `product_id` INT(11) NOT NULL , INDEX `fk_product_category_category1_zxc` (`category_id` ASC) , CONSTRAINT `fk_product_category_category1_zxc` FOREIGN KEY (`category_id` ) REFERENCES `shop`.`category` (`category_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci; 

Error code: 1005. Unable to create table "shop.product_category" (errno: 150)

Answer 1

In the category table, you need the category_id index (I see it as part of the primary key, but since it is the second column in the index, it cannot be used). The field that you refer to in the foreign key must always be indexed.

Answer 2

In my case, the problem was more like what was described in the first article you contacted.

Therefore, I just needed to make sure that:

• Referenced Column is an index,
• both Referencing Column and Referenced Column are of the same type and length, i.e. both INT(10) values INT(10) ,
• both have the same settings not null , unsigned , zerofill , etc.
• both tables: InnoDB !

Here's the query pattern where the Referencing Column is equal to referencing_id and the Referenced Column is equal to referenced_id :

 ALTER TABLE `db`.`referencing` ADD CONSTRAINT `my_fk_idx` FOREIGN KEY (`referencing_id`) REFERENCES `db`.`referenced`(`referenced_id`) ON DELETE NO ACTION ON UPDATE NO ACTION; 

Update 2016-03-13 : re-included in this issue, and I found my own answer. This time it did not help. It turns out that another table is still set to MyISAM, as soon as I changed it to InnoDB, everything worked.