MySQL table creation

Question

Below is the code I used in MySQL workbench to create the database as well as the tables. Finally I have mentioned the error I face. Could anyone please help me with this?

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

Employee Table :

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$$

Inventory Table :

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$$

Product Details Table :

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$$

Trans In Table :

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$$

All the four above tables are created.

TransOut Tablle :

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$$

The last table n creation, shows an error as

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

Could anyone please help me?

FYI, There is no need to change the DELIMITER to create tables — Michael Berkowski, Jun 08 '14 at 16:43
possible duplicate of [MySQL errorno 121](http://stackoverflow.com/questions/1180660/mysql-errorno-121) — Daniel W., Jun 08 '14 at 16:43
Something is amiss with the `inventory` table as you have pasted the defintion here. The column `MID` follows the table options... — Michael Berkowski, Jun 08 '14 at 16:44
I can create these tables correctly _if_ I create `productdetail` _before_ `inventory`, unlike the order you pasted above http://sqlfiddle.com/#!2/73c57 — Michael Berkowski, Jun 08 '14 at 16:47
possible duplicate of [ERROR: Error 1005: Can't create table (errno: 121)](http://stackoverflow.com/questions/12623651/error-error-1005-cant-create-table-errno-121) — PM 77-1, Jun 09 '14 at 01:13
@PM77-1 Indeed, I missed that one, thanks for noticing. It's creatable if the names `ItemID,EmpID` are changed in the FK constraints... http://sqlfiddle.com/#!2/5446c7 — Michael Berkowski, Jun 09 '14 at 01:35

score 3 · Accepted Answer · answered Jun 08 '14 at 16:58

Check out this post : MySQL errorno 121

As said :

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

So I think that you may use another name into your TransOut table for ItemID & EmpID

MySQL table creation

1 Answers1