SQL-错误代码1005，错误号为121 [英] SQL - error code 1005 with error number 121

问题描述

我正在运行由 MySQL Workbench 自动生成的以下MySQL脚本(已精简)，并且出现以下错误:

I'm running the following MySQL script (trimmed down), generated automatically by MySQL Workbench and I get the following error:

错误代码:1005 无法创建表"regula.reservation"(错误号:121)

Error Code: 1005 Can't create table 'regula.reservation' (errno: 121)

我对数据库不是很熟练，并且此错误也不是非常有用.
这是什么问题?

I'm not very proficient with databases and this error is not very informative.
What is the problem here?

-- -----------------------------------------------------
-- Table `regula`.`Users`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `regula`.`Users` ;

CREATE  TABLE IF NOT EXISTS `regula`.`Users` (
  `idUsers` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` TEXT NOT NULL ,
  `type` TEXT NOT NULL ,
  `pwd` TEXT NOT NULL ,
  PRIMARY KEY (`idUsers`) ,
  UNIQUE INDEX `idUsers_UNIQUE` (`idUsers` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `regula`.`Projects`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `regula`.`Projects` ;

CREATE  TABLE IF NOT EXISTS `regula`.`Projects` (
  `idProjects` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `ownerId` INT UNSIGNED NOT NULL ,
  `name` TEXT NOT NULL ,
  `date` DATE NOT NULL ,
  `time` TIME NOT NULL ,
  `place` TEXT NOT NULL ,
  `itemType` INT NOT NULL ,
  PRIMARY KEY (`idProjects`) ,
  UNIQUE INDEX `idProjects_UNIQUE` (`idProjects` ASC) ,
  INDEX `ownerId` (`ownerId` ASC) ,
  CONSTRAINT `ownerId`
    FOREIGN KEY (`ownerId` )
    REFERENCES `regula`.`Users` (`idUsers` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `regula`.`ItemTypes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `regula`.`ItemTypes` ;

CREATE  TABLE IF NOT EXISTS `regula`.`ItemTypes` (
  `idItemTypes` INT UNSIGNED NOT NULL ,
  `prjId` INT UNSIGNED NOT NULL ,
  `parentId` INT UNSIGNED NULL DEFAULT NULL ,
  `name` TEXT NOT NULL ,
  PRIMARY KEY (`idItemTypes`) ,
  INDEX `prjId` (`prjId` ASC) ,
  INDEX `parentId` (`parentId` ASC) ,
  CONSTRAINT `prjId`
    FOREIGN KEY (`prjId` )
    REFERENCES `regula`.`Projects` (`idProjects` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `parentId`
    FOREIGN KEY (`parentId` )
    REFERENCES `regula`.`ItemTypes` (`idItemTypes` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `regula`.`Reservation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `regula`.`Reservation` ;

CREATE  TABLE IF NOT EXISTS `regula`.`Reservation` (
  `idReservation` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `prjId` INT UNSIGNED NOT NULL ,
  `itemTypeId` INT UNSIGNED NOT NULL ,
  `userId` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`idReservation`) ,
  INDEX `prjId` (`prjId` ASC) ,
  INDEX `itemTypeId` (`itemTypeId` ASC) ,
  INDEX `userId` (`userId` ASC) ,
  CONSTRAINT `prjId`
    FOREIGN KEY (`prjId` )
    REFERENCES `regula`.`Projects` (`idProjects` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `itemTypeId`
    FOREIGN KEY (`itemTypeId` )
    REFERENCES `regula`.`ItemTypes` (`idItemTypes` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `userId`
    FOREIGN KEY (`userId` )
    REFERENCES `regula`.`Users` (`idUsers` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

推荐答案

错误121表示存在外键约束错误.由于您使用的是InnoDB，因此在运行失败的查询后可以在LATEST FOREIGN KEY ERROR部分中使用SHOW ENGINE INNODB STATUS.我自己运行了SQL，我得到了:

Error 121 means that there is a foreign key constraint error. Since you're using InnoDB, you can use SHOW ENGINE INNODB STATUS after running the failed query to get an explanation in the LATEST FOREIGN KEY ERROR section. Having run your SQL myself, I get this:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
101210 14:55:50 Error in foreign key constraint creation for table `regula`.`Reservation`.
A foreign key constraint of name `regula`.`prjId`
already exists. (Note that internally InnoDB adds 'databasename'
in front of the user-defined constraint name.)
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

基本上，您需要在最后一个表中给prjId约束名称一个唯一的名称.约束/外键名称对于数据库是全局的，因此它们不能在不同的表中重复使用.只需更改最后一个

Basically, you need to give your prjId constraint name a unique name in the last table. Constraint/foreign key names are global to a database, so they cannot be reused in different tables. Just change the last

  CONSTRAINT `prjId`

到

  CONSTRAINT `prjId2`

这篇关于SQL-错误代码1005，错误号为121的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！