SQL-错误代码1005,错误号为121

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

1005无法创建表“ regula.reservation”(错误号:121)

我对数据库不是很精通,并且此错误也不是非常有用。

这里有什么问题?

-- -----------------------------------------------------

-- 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,因此您可以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.

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

  CONSTRAINT `prjId`

  CONSTRAINT `prjId2`

以上是 SQL-错误代码1005,错误号为121 的全部内容, 来源链接: utcz.com/qa/420247.html

回到顶部