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.