MySQL建表

Question

下面是我在 MySQL Workbench 中用来创建数据库和表的代码。最后我提到了我面临的错误。谁能帮我解决这个问题？

delimiter $$
CREATE DATABASE `sa38team4` /*!40100 DEFAULT CHARACTER SET utf8 */$$

员工表:

delimiter $$
 CREATE TABLE `employee` (
  `EmpID` int(11) NOT NULL AUTO_INCREMENT,
  `EmpName` varchar(100) NOT NULL,
  `DOB` datetime NOT NULL,
  `Gender` varchar(10) NOT NULL,
  `Password` varchar(50) NOT NULL,
  `ContactNumber` varchar(45) NOT NULL,
  `Email` varchar(45) DEFAULT NULL,
  `Role` varchar(15) NOT NULL,
  `Address` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`EmpID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

库存表:

delimiter $$
CREATE TABLE `inventory` (
  `ItemID` int(11) NOT NULL AUTO_INCREMENT,
  `PID` int(11) NOT NULL,
  `TotalQty` int(11) NOT NULL,
  `Availability` int(11) NOT NULL,
  `ReorderPt` int(11) NOT NULL,
  `MinOrdQty` int(11) NOT NULL,
  `UnitPrice` decimal(10,2) NOT NULL,
  PRIMARY KEY (`ItemID`),
  KEY `PID` (`PID`),
  CONSTRAINT `PID` FOREIGN KEY (`PID`) REFERENCES `productdetail` (`PID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
  `MID` varchar(45) NOT NULL,
  `Manufacturer` varchar(100) NOT NULL,
  `Model` varchar(45) NOT NULL,
  `PartNo` varchar(45) NOT NULL,
  `ItemDesc` varchar(100) NOT NULL,
  PRIMARY KEY (`PID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

产品详情表:

delimiter $$
CREATE TABLE `productdetail` (
  `PID` int(11) NOT NULL AUTO_INCREMENT,
  `MID` varchar(45) NOT NULL,
  `Manufacturer` varchar(100) NOT NULL,
  `Model` varchar(45) NOT NULL,
  `PartNo` varchar(45) NOT NULL,
  `ItemDesc` varchar(100) NOT NULL,
  PRIMARY KEY (`PID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

在表中传输:

delimiter $$
CREATE TABLE `transin` (
  `TransID` int(11) NOT NULL AUTO_INCREMENT,
  `Date` datetime NOT NULL,
  `EmpID` int(11) NOT NULL,
  `ItemID` int(11) NOT NULL,
  `Qty` int(11) NOT NULL,
  `UnitPrice` decimal(10,2) NOT NULL,
  `Remark` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`TransID`),
  KEY `EmpID` (`EmpID`),
  KEY `ItemID` (`ItemID`),
  CONSTRAINT `EmpID` FOREIGN KEY (`EmpID`) REFERENCES `employee` (`EmpID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `ItemID` FOREIGN KEY (`ItemID`) REFERENCES `inventory` (`ItemID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

上面的四个表都创建好了。

转出表:

delimiter $$
CREATE TABLE `transout` (
  `TransID` int(11) NOT NULL AUTO_INCREMENT,
  `Date` datetime NOT NULL,
  `EmpID` int(11) NOT NULL,
  `ItemID` int(11) NOT NULL,
  `Qty` int(11) NOT NULL,
  `UnitPrice` decimal(10,2) NOT NULL,
  `Remark` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`TransID`),
  KEY `EmpID` (`EmpID`),
  KEY `ItemID` (`ItemID`),
  CONSTRAINT `EmpID` FOREIGN KEY (`EmpID`) REFERENCES `employee` (`EmpID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `ItemID` FOREIGN KEY (`ItemID`) REFERENCES `inventory` (`ItemID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

最后创建n表，报错为

"Error Code : 1005. Can't create table 'sa38team4.transout'(errno: 121)"

谁能帮帮我？

Answer 1

查看这篇文章:MySQL errorno 121

如前所述:

Check that all your constraints are really spelled out correctly, also check that there's not any other tables that uses the constraint names ItemID or EmpID

所以我认为您可以在 TransOut 表中为 ItemID 和 EmpID 使用另一个名称