错误1005：无法创建表（错误150）

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

I have made sure the values are the same with the foreign key (pic_id) and the primary key in my houses table. I have created the indexes as well So i'm really not sure where this error is coming. If anyone can provide any other explain why the Error 1005 happens that would be really helpful. Thank you.

我已确保值与外键(pic_id)和my house表中的主键相同。我也创建了索引所以我真的不确定这个错误会在哪里发生。如果任何人都可以提供任何其他解释为什么错误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;

1 个解决方案

#1

I just created an index fk_houses_pics_idx and it works. Here is the updated code:

我刚刚创建了一个索引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;

So why this worked? When creating a foreign key constraint, MySQL requires a usable index on both the referencing table and also on the referenced table. The index on the referencing table is created automatically if one doesn't exist, but the one on the referenced table needs to be created manually (Source). Yours appears to be missing.

那么为什么这样呢?在创建外键约束时,MySQL需要在引用表和引用表上都有可用的索引。如果不存在,则会自动创建引用表上的索引,但需要手动创建引用表上的索引(源)。你的似乎不见了。

So, It seems that you were missing index for pic_id on houses table. Adding it solved the problem.

所以,似乎你错过了house表上pic_id的索引。添加它解决了这个问题。

#1