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

最新推荐文章于 2023-05-12 21:04:44 发布

转载最新推荐文章于 2023-05-12 21:04:44 发布 · 194 阅读

文章标签：

本文介绍了一种常见的MySQL数据库错误——外键约束冲突错误121，并提供了解决方案。通过修改表定义中重复的约束名称，可以避免创建表时出现错误。

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

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;

解决方案

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.

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`