无法创建表（errno：150）InnoDB添加外键约束

发布于 2024-12-01 10:28:08 字数 1342 浏览 2 评论 0原文

真的很讨厌占用别人的时间，但问题似乎并没有消失。

我考虑了 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 ,
  ...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;

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

原文

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)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

羁〃客ぐ 2024-12-08 10:28:08

回复收藏 0 原文

我要还你自由 2024-12-08 10:28:08

continue

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.

回复收藏 0 原文

~没有更多了~