Can't create table (errno: 150) InnoDB adding foreign key constraints

Question

Really hate to use other people's time, but it seems the problem is just not going away.

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

hope that someone points to a stupid 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. Can't create table 'shop.product_category' (errno: 150)

Answer 1

You need an index on category_id in the category table (I see it's part of the primary key, but since it's the second column in the index, it can not be used). The field you are referencing in a foreign key always should be indexed.

Answer 2

In my case the issue was more like what was described in the first article you've linked to.

So I just had to make sure that:

• Referenced Column is an index,
• both Referencing Column and Referenced Column share the same type and length, i.e. e.g. both are INT(10),
• both share the same not null, unsigned, zerofill etc. configuration.
• both tables are InnoDB!

Here's the query template where Referencing Column is referencing_id and Referenced Column is 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: Ran into this problem again, ended up finding my own answer. This time it didn't help though. Turns out the other table was still set to MyISAM, as soon as I changed it to InnoDB everything worked.