无法创建表（errno：150）InnoDB添加外键约束 [英] Can't create table (errno: 150) InnoDB adding foreign key constraints

问题描述

我认为所有建议都在 http://verysimple.com/2006/ 10/22 / mysql-error-number-1005-cant-create-table-mydbsql -328_45frm-errno-150 / 和 http://forums.mysql.com/read.php?22,19755,19755#msg-19755 但没有任何意义。

希望有人指出一个愚蠢的错误。

这里是表格：

  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，
 ...其他列... 
 PRIMARY KEY（`id`，`category_id`））
 ENGINE = InnoDB 
 DEFAULT CHARACTER SET = utf8 
 COLLATE = utf8_unicode_ci; 
 
 
 $ b 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; 
  

错误代码：1005.无法创建表'shop.product_category'（errno：150）

解决方案

你需要在类别表中的category_id的索引（我看到它是主键的一部分，但是因为它是第二列在索引中，它不能被使用）。您在外键中引用的字段应始终索引。

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)

解决方案

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.

这篇关于无法创建表（errno：150）InnoDB添加外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！