CODEWITHSUNDEEP
mysqlsqlconstraints
user1834200
user1834200

Reputation: 162

Error Code: 1215: Cannot add foreign key constraint

So, I have created the following tables in MySQL.

    CREATE TABLE `StockTransaction` (
    `TransactionID` int(11),
    `Fee` decimal(6,2),
    `DateTime` datetime DEFAULT NULL,
    `PricePerShare` decimal(6,2),
    PRIMARY KEY (`TransactionID`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# Dump of table StockOrder
# ------------------------------------------------------------

DROP TABLE IF EXISTS `StockOrder`;

CREATE TABLE `StockOrder` (
  `OrderID` int(11) NOT NULL,
  `OrderStockSymbol` char(6) NOT NULL,
  `OrderType` enum('buy','sell') DEFAULT NULL,
  `NumOfShares` int(11) DEFAULT NULL,
  `OrderCustomerAccNum` int(11) unsigned NOT NULL,
  `DateOfOrder` datetime DEFAULT NULL,
  `PriceType` enum('Market','MarketOnClose','TrailingStop','HiddenStop') NOT NULL,
  `OrderEmployeeID` int(11) unsigned DEFAULT NULL,
  `OrderCustomerID` int(11) unsigned NOT NULL,
  `OrderTransactionID` int(11) unsigned,
  `Type` char(30) NOT NULL DEFAULT 'Default',
  PRIMARY KEY (`OrderID`,`Type`),
  CONSTRAINT `stockorder_ibfk_1` FOREIGN KEY (`OrderStockSymbol`) REFERENCES `Stock` (`StockSymbol`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `stockorder_ibfk_2` FOREIGN KEY (`OrderCustomerAccNum`) REFERENCES `Account` (`AccountNumber`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `stockorder_ibfk_3` FOREIGN KEY (`OrderCustomerID`) REFERENCES `Customer` (`SSN`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `stockorder_ibfk_4` FOREIGN KEY (`OrderEmployeeID`) REFERENCES `Employee` (`EmployeeSSN`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `stockorder_ibfk_5` FOREIGN KEY (`OrderTransactionID`) REFERENCES `StockTransaction` (`TransactionID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

For whatever reason, if I comment out the creation of the constraint 'stockorder_ibfk_5', this code works, but for the life of me I can't figure out why. What exactly is going on, and why can't I create the foreign key to StockTransaction.TransactionID?

Upvotes: 0

Views: 29

Answers (2)

Zafar Malik
Zafar Malik

Reputation: 6854

On which field you are creating foreign key and referenced field schema should be exact same here one is signed (default) and another is unsigned. So your schema should be as per below-

CREATE TABLE `StockTransaction` (
    `TransactionID` int(11) unsigned,
    `Fee` decimal(6,2),
    `DateTime` datetime DEFAULT NULL,
    `PricePerShare` decimal(6,2),
    PRIMARY KEY (`TransactionID`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# Dump of table StockOrder
# ------------------------------------------------------------

DROP TABLE IF EXISTS `StockOrder`;

CREATE TABLE `StockOrder` (
  `OrderID` int(11) NOT NULL,
  `OrderStockSymbol` char(6) NOT NULL,
  `OrderType` enum('buy','sell') DEFAULT NULL,
  `NumOfShares` int(11) DEFAULT NULL,
  `OrderCustomerAccNum` int(11) unsigned NOT NULL,
  `DateOfOrder` datetime DEFAULT NULL,
  `PriceType` enum('Market','MarketOnClose','TrailingStop','HiddenStop') NOT NULL,
  `OrderEmployeeID` int(11) unsigned DEFAULT NULL,
  `OrderCustomerID` int(11) unsigned NOT NULL,
  `OrderTransactionID` int(11) unsigned,
  `Type` char(30) NOT NULL DEFAULT 'Default',
  PRIMARY KEY (`OrderID`,`Type`),
  CONSTRAINT `stockorder_ibfk_1` FOREIGN KEY (`OrderStockSymbol`) REFERENCES `Stock` (`StockSymbol`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `stockorder_ibfk_2` FOREIGN KEY (`OrderCustomerAccNum`) REFERENCES `Account` (`AccountNumber`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `stockorder_ibfk_3` FOREIGN KEY (`OrderCustomerID`) REFERENCES `Customer` (`SSN`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `stockorder_ibfk_4` FOREIGN KEY (`OrderEmployeeID`) REFERENCES `Employee` (`EmployeeSSN`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `stockorder_ibfk_5` FOREIGN KEY (`OrderTransactionID`) REFERENCES `StockTransaction` (`TransactionID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Note: Further I think you would like to keep your primary keys in both tables as auto_increment but it is as per your requirement not neccessary.

Upvotes: 1

Darwin von Corax
Darwin von Corax

Reputation: 5256

The types don't match. StockOrder.OrderTransactionID is unsigned, and StockTransaction.TransactionID is signed. Make them both either signed or unsigned, and you should be shiny.

Upvotes: 1

Related Questions