MySQL 1215：无法添加外键约束(MySQL 1215: Cannot add foreign key constraint)

MySQL 1215：无法添加外键约束(MySQL 1215: Cannot add foreign key constraint)

你能否提出为什么CHARGEITEM，INVOICE和PAYMENT可能会失败？ 我在这里错过了什么？

我检查了这些：

所有PK和FK都是BIGINT列 我做了可空列 删除了约束名称（认为它们可能会发生冲突） 添加了DEL DELETE RESTRICT 我也检查了他们都是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;

Could you suggest why CHARGEITEM, INVOICE and PAYMENT might be failing? What am I missing here?

I checked these:

All PKs and FKs are BIGINT columns I made them nullable columns Removed the constraint names (thinking they might be clashing) Added ON DELETE RESTRICT I also checked that all of them are INNODB.

Here is the full script (tables 6, 8 and 9 are failing):

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;

最满意答案

两个问题：

当您在其他表格中引用时， INVOICES不存在。 在引用之前定义表格或在创建所有表格后定义约束。

在INVOICES表中，您正在定义数据类型varchar（255），但在外键中引用bigint列 - 不起作用。

在INVOICES表中，

`BOOKINGID` varchar(255) NOT NULL,

将其更改为：

`BOOKINGID` BIGINT NOT NULL,

固定在这里 -

http://rextester.com/JAZOUD38011

Two issues:

INVOICES doesnt exists when you are referring it in another table. Either define the table before referring or define constraints after all the tables are created.

in INVOICES table, you are defining data type varchar(255) but in foreign key, it refers to a bigint column - won't work.

In INVOICES table,

`BOOKINGID` varchar(255) NOT NULL,

change it to:

`BOOKINGID` BIGINT NOT NULL,

Fixed both here -

http://rextester.com/JAZOUD38011

MySQL 1215：无法添加外键约束(MySQL 1215: Cannot add foreign key constraint)

你能否提出为什么CHARGEITEM，INVOICE和PAYMENT可能会失败？ 我在这里错过了什么？

我检查了这些：

所有PK和FK都是BIGINT列 我做了可空列 删除了约束名称（认为它们可能会发生冲突） 添加了DEL DELETE RESTRICT 我也检查了他们都是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;

Could you suggest why CHARGEITEM, INVOICE and PAYMENT might be failing? What am I missing here?

I checked these:

All PKs and FKs are BIGINT columns I made them nullable columns Removed the constraint names (thinking they might be clashing) Added ON DELETE RESTRICT I also checked that all of them are INNODB.

Here is the full script (tables 6, 8 and 9 are failing):

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;

最满意答案

两个问题：

当您在其他表格中引用时， INVOICES不存在。 在引用之前定义表格或在创建所有表格后定义约束。

在INVOICES表中，您正在定义数据类型varchar（255），但在外键中引用bigint列 - 不起作用。

在INVOICES表中，

`BOOKINGID` varchar(255) NOT NULL,

将其更改为：

`BOOKINGID` BIGINT NOT NULL,

固定在这里 -

http://rextester.com/JAZOUD38011

Two issues:

INVOICES doesnt exists when you are referring it in another table. Either define the table before referring or define constraints after all the tables are created.

in INVOICES table, you are defining data type varchar(255) but in foreign key, it refers to a bigint column - won't work.

In INVOICES table,

`BOOKINGID` varchar(255) NOT NULL,

change it to:

`BOOKINGID` BIGINT NOT NULL,

Fixed both here -

http://rextester.com/JAZOUD38011