MySQL 1215 : Cannot add foreign key constraint

Question

您能否说明为什么 CHARGEITEM、INVOICE 和 PAYMENT 可能会失败？我在这里缺少什么？

我检查了这些:

• 所有 PK 和 FK 都是 BIGINT 列
• 我将它们设为可为空的列
• 删除了约束名称(认为它们可能会发生冲突)
• 添加到删除限制
• 我还检查过它们都是 INNODB。

这是完整的脚本(表 6、8 和 9 失败):

CREATE TABLE `BUILDING` (
  `ID` BIGINT NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) DEFAULT NULL,
  `ADDRESS` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `ROOM` (
  `ID` BIGINT NOT NULL AUTO_INCREMENT,
  `BUILDINGID` BIGINT NULL DEFAULT NULL,
  `FLOORNUM` varchar(255) DEFAULT NULL,
  `DOORNUM` varchar(255) DEFAULT NULL,
  `TYPE` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `BUILDINGID` (`BUILDINGID`),
  FOREIGN KEY (`BUILDINGID`) REFERENCES `BUILDING` (`ID`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `BOOKING` (
  `ID` BIGINT NOT NULL AUTO_INCREMENT,
  `ENTRYDATE` datetime DEFAULT NULL,
  `GUESTNAME` varchar(255) DEFAULT NULL,
  `GUESTCONTACT` varchar(255) DEFAULT NULL,
  `GUESTADDRESS` varchar(255) DEFAULT NULL,
  `GUESTIDTYPE` varchar(255) DEFAULT NULL,
  `GUESTIDNUM` varchar(255) DEFAULT NULL,
  `GUESTPASSPORTNUM` varchar(255) DEFAULT NULL,
  `NOTES` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `BOOKINGROOM` (
  `ID` BIGINT NOT NULL AUTO_INCREMENT,
  `BOOKINGID` BIGINT NULL DEFAULT NULL,
  `ROOMID` BIGINT NULL DEFAULT NULL,
  `STARTDATE` date DEFAULT NULL,
  `ENDDATE` date DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `BOOKINGID` (`BOOKINGID`),
  KEY `ROOMID` (`ROOMID`),
  FOREIGN KEY (`BOOKINGID`) REFERENCES `BOOKING` (`ID`) ON DELETE RESTRICT,
  FOREIGN KEY (`ROOMID`) REFERENCES `ROOM` (`ID`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `CHARGE` (
  `ID` BIGINT NOT NULL AUTO_INCREMENT,
  `CHARGEGROUP` varchar(255) DEFAULT NULL,
  `CHARGECODE` varchar(255) DEFAULT NULL,
  `NOTES` varchar(255) DEFAULT NULL,
  `UNIT` varchar(255) DEFAULT NULL,
  `UNITRATE` double NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `CHARGEITEM` (
  `ID` BIGINT NOT NULL AUTO_INCREMENT,
  `BOOKINGID` BIGINT NULL DEFAULT NULL,
  `ROOMID` BIGINT NULL DEFAULT NULL,
  `CHARGEID` BIGINT NULL DEFAULT NULL,
  `ENTRYSTAFFID` BIGINT NULL DEFAULT NULL,
  `ENTRYDATE` datetime DEFAULT NULL,
  `VALUEDATE` date DEFAULT NULL,
  `UNIT` varchar(255) DEFAULT NULL,
  `UNITRATE` double NOT NULL,
  `UNITS` double NOT NULL,
  `AMOUNT` double NOT NULL,
  `NOTES` varchar(255) DEFAULT NULL,
  `INVOICEID` BIGINT NULL DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `INVOICEID` (`INVOICEID`),
  KEY `ROOMID` (`ROOMID`),
  KEY `BOOKINGID` (`BOOKINGID`),
  KEY `CHARGEID` (`CHARGEID`),
  FOREIGN KEY (`INVOICEID`) REFERENCES `INVOICE` (`ID`) ON DELETE RESTRICT,
  FOREIGN KEY (`ROOMID`) REFERENCES `ROOM` (`ID`) ON DELETE RESTRICT,
  FOREIGN KEY (`BOOKINGID`) REFERENCES `BOOKING` (`ID`) ON DELETE RESTRICT,
  FOREIGN KEY (`CHARGEID`) REFERENCES `CHARGE` (`ID`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `DROPDOWNENTRY` (
  `ID` BIGINT NOT NULL AUTO_INCREMENT,
  `MODULE` varchar(255) DEFAULT NULL,
  `POSITION` int(11) NOT NULL,
  `KEY` varchar(255) DEFAULT NULL,
  `VALUE` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `INVOICE` (
  `ID` BIGINT NOT NULL AUTO_INCREMENT,
  `BOOKINGID` varchar(255) NOT NULL,
  `ENTRYSTAFFID` BIGINT NULL DEFAULT NULL,
  `ENTRYDATE` datetime DEFAULT NULL,
  `AUTHSTAFFID` BIGINT NULL DEFAULT NULL,
  `AUTHDATE` datetime DEFAULT NULL,
  `NETAMOUNT` double NOT NULL,
  `TAX1` double NOT NULL,
  `TAX2` double NOT NULL,
  `TAX3` double NOT NULL,
  `TOTALTAX` double NOT NULL,
  `TOTALINCLTAX` double NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `BOOKINGID` (`BOOKINGID`),
  FOREIGN KEY (`BOOKINGID`) REFERENCES `BOOKING` (`ID`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `PAYMENT` (
  `ID` BIGINT NOT NULL AUTO_INCREMENT,
  `INVOICEID` BIGINT NULL DEFAULT NULL,
  `AMOUNTPAID` double NOT NULL,
  `PAYMODE` varchar(255) DEFAULT NULL,
  `ENTRYDATE` datetime DEFAULT NULL,
  `VALUEDATE` date DEFAULT NULL,
  `REALISATIONDATE` date DEFAULT NULL,
  `BANKCODE` varchar(255) DEFAULT NULL,
  `INSTRUMENTNUM` varchar(255) DEFAULT NULL,
  `POSCODE` varchar(255) DEFAULT NULL,
  `REALISATIONSTATUS` varchar(255) DEFAULT NULL,
  `NOTES` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `INVOICEID` (`INVOICEID`),
  FOREIGN KEY (`INVOICEID`) REFERENCES `INVOICE` (`ID`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `RESERVATION` (
  `ID` BIGINT NOT NULL AUTO_INCREMENT,
  `ENTRYDATE` datetime DEFAULT NULL,
  `FROMDATE` date DEFAULT NULL,
  `TODATE` date DEFAULT NULL,
  `NUMROOMS` int(11) NOT NULL,
  `GUESTNAME` varchar(255) DEFAULT NULL,
  `GUESTCONTACT` varchar(255) DEFAULT NULL,
  `GUESTADDRESS` varchar(255) DEFAULT NULL,
  `NOTES` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 1

两个问题:

• INVOICES 当您在另一个表中引用它时不存在。在引用之前定义表或在创建所有表后定义约束。

• 在 INVOICES 表中，您正在定义数据类型 varchar(255)，但在外键中，它指的是 bigint 列 - 不起作用。

  在 INVOICES 表中，

  `BOOKINGID` varchar(255) NOT NULL,
  

  将其更改为:

  `BOOKINGID` BIGINT NOT NULL,
  

这里都修复了 -

http://rextester.com/JAZOUD38011