MySQL SQL - 错误代码 1005，错误号 121

SQL - error code 1005 with error number 121

提问by Amir Rachum

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

我正在运行由MySQL Workbench自动生成的以下 MySQL 脚本（已删减），但出现以下错误：

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

错误代码：1005 无法创建表“regula.reservation”（错误号：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;

回答by Jonathan Amend

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

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

------------------------
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.

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

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

  CONSTRAINT `prjId`

to

到

  CONSTRAINT `prjId2`

回答by Pankaj Singh

The error code 121 comes when you try to map the foreign key.

当您尝试映射外键时，会出现错误代码 121。

Basically it comes when your foreign key name already exists in the database.

基本上，当您的外键名称已存在于数据库中时，它就会出现。

For example:

例如：

ALTER TABLE `photokiosk`.`kiosk_event`
    ADD CONSTRAINT `event_booking_id`
    FOREIGN KEY `event_booking_id` (`event_booking_id`)
    REFERENCES `event_booking` (`event_booking_id`)

If foreign key with the name event_booking_idis already mapped with the other table.

如果名称为event_booking_id 的外键已经映射到另一个表。

To get rid of this issue, please change the foreign key name, not the column name.

要解决此问题，请更改外键名称，而不是列名称。

回答by Dhaval

You will get this error message if you try to use a constraint name which already exists in the table.

如果您尝试使用表中已存在的约束名称，您将收到此错误消息。

Here 'prjId' already exists in table regula.ItemTypes. So, you can't use the same constraint name on table 'regula.reservation' again.

这里 'prjId' 已经存在于表中regula。ItemTypes. 因此，您不能再次在表 'regula.reservation' 上使用相同的约束名称。