我正在运行以下MySQL查询(剪裁),由MySQL工作台自动生成,并得到以下错误:
错误代码: 1005无法创建表“regula.reservation”(errno: 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;发布于 2010-12-10 20:02:16
错误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`发布于 2011-12-08 13:05:49
当您试图映射外键时,会出现错误代码121。
基本上,它是在数据库中已经存在外键名称时出现的。
例如:
ALTER TABLE `photokiosk`.`kiosk_event`
ADD CONSTRAINT `event_booking_id`
FOREIGN KEY `event_booking_id` (`event_booking_id`)
REFERENCES `event_booking` (`event_booking_id`)如果名为event_booking_id的外键已与另一个表映射。
若要解决此问题,请更改外键名称,而不是列名。
发布于 2014-02-27 00:14:49
如果尝试使用表中已经存在的约束名称,则会收到此错误消息。
这里'prjId‘已经存在于表regula.ItemTypes中。因此,您不能再次在表'regula.reservation‘上使用相同的约束名称。
https://stackoverflow.com/questions/4412693
复制相似问题