我正在运行以下由 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,我得到了:
SHOW ENGINE INNODB STATUS
LATEST FOREIGN KEY ERROR
------------------------ 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`