mysql - 错误 1005 : Can't Create table (errno 150)

Question

ERROR: Error 1005: Can't create table 'db.pics' (errno: 150)
SQL Code:
        CREATE TABLE IF NOT EXISTS `db`.`pics` (
          `pic_id` INT NOT NULL COMMENT '',
          PRIMARY KEY (`pic_id`)  COMMENT '',
          CONSTRAINT `fk_pics_houses1`
            FOREIGN KEY (`pic_id`)
            REFERENCES `db`.`houses` (`pic_id`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB

我已确保值与外键 (pic_id) 和 houses 表中的主键相同。我也创建了索引所以我真的不确定这个错误是从哪里来的。如果有人可以提供任何其他解释为什么会发生错误 1005，那将非常有帮助。谢谢。

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `db` DEFAULT CHARACTER SET latin1 ;
USE `db` ;

DROP TABLE IF EXISTS `db`.`users` ;

CREATE TABLE IF NOT EXISTS `db`.`users` (
  `user_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '',
  PRIMARY KEY (`user_id`)  COMMENT '',
ENGINE = InnoDB
AUTO_INCREMENT = 24
DEFAULT CHARACTER SET = latin1;

DROP TABLE IF EXISTS `db`.`houses` ;

CREATE TABLE IF NOT EXISTS `db`.`houses` (
  `house_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '',
  `user_id` INT(11) NOT NULL COMMENT '',
  `pic_id` INT(11) NOT NULL COMMENT '',
  PRIMARY KEY (`house_id`)  COMMENT '',
  CONSTRAINT `fk_houses_users1`
    FOREIGN KEY (`user_id`)
    REFERENCES `db`.`users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 25
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `fk_houses_users1_idx` ON `db`.`houses` (`user_id` ASC)  COMMENT '';

DROP TABLE IF EXISTS `db`.`pics` ;

CREATE TABLE IF NOT EXISTS `db`.`pics` (
  `pic_id` INT NOT NULL COMMENT '',
  PRIMARY KEY (`pic_id`)  COMMENT '',
  CONSTRAINT `fk_pics_houses1`
    FOREIGN KEY (`pic_id`)
    REFERENCES `db`.`houses` (`pic_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Answer 1

我刚刚创建了一个索引 fk_houses_pics_idx 并且它有效。这是更新后的代码:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `db` DEFAULT CHARACTER SET latin1 ;
USE `db` ;

DROP TABLE IF EXISTS `db`.`users` ;

CREATE TABLE IF NOT EXISTS `db`.`users` (
  `user_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '',
  PRIMARY KEY (`user_id`)  COMMENT ''
)
ENGINE = InnoDB
AUTO_INCREMENT = 24
DEFAULT CHARACTER SET = latin1;

DROP TABLE IF EXISTS `db`.`houses` ;

CREATE TABLE IF NOT EXISTS `db`.`houses` (
  `house_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '',
  `user_id` INT(11) NOT NULL COMMENT '',
  `pic_id` INT(11) NOT NULL COMMENT '',
  PRIMARY KEY (`house_id`)  COMMENT '',
  CONSTRAINT `fk_houses_users1`
    FOREIGN KEY (`user_id`)
    REFERENCES `db`.`users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 25
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `fk_houses_users1_idx` ON `db`.`houses` (`user_id` ASC)  COMMENT '';
CREATE INDEX `fk_houses_pics_idx` ON `db`.`houses` (`pic_id` ASC)  COMMENT ''; /*This is what I just added */

DROP TABLE IF EXISTS `db`.`pics` ;

CREATE TABLE IF NOT EXISTS `db`.`pics` (
  `pic_id` INT NOT NULL COMMENT '',
  PRIMARY KEY (`pic_id`)  COMMENT '',
  CONSTRAINT `fk_pics_houses1`
    FOREIGN KEY (`pic_id`)
    REFERENCES `db`.`houses` (`pic_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

那么为什么这有效？创建外键约束时，MySQL 需要在引用表和被引用表上都有一个可用的索引。如果引用表上的索引不存在，则会自动创建索引，但需要手动创建引用表上的索引 ( Source )。你的好像不见了。

所以，您似乎缺少 houses 表上 pic_id 的索引。添加它解决了问题。