MySQL 错误代码 1215

Question

我有以下架构:WilliamsSCW，但我不断收到错误:1215 无法为计费表添加外键约束。

请帮忙。我应该怎么办？

Create database WilliamsSCW;

Create table WilliamsSCW.Login
(
UserName varchar(50) Not Null Primary Key,
Password varchar(50) Not Null);

Create table WilliamsSCW.Employee
(
employeeID int(8) Not Null Primary Key,
employeeFName varchar(25),
employeeLName varchar(25));

Create table WilliamsSCW.Customer
(
custID int(8) Not Null Primary Key,
custFName varchar(25),
custLName varchar(25),
custAddress varchar(30),
custCity varchar(30),
custState varchar(30),
custZip int(5),
custPhone BIGINT(10),
custEmail text);

Create table WilliamsWSC.Order1  #order is a reserved key word in MySQL and can't be used
(
orderID int(20) Not Null Primary Key,
orderDesc varchar(30),
orderType varchar(20),
orderCost int(10),
orderQuantity int(6),
orderContent text,
orderStatus varchar(10),
orderDate TimeStamp,
orderShipDate Date,
orderDeposit decimal(10,2),
mediaID BIGINT(20),
custID int(8),
employeeID int(8),
foreign key(mediaID) REFERENCES Media_Catalog(mediaID) ON UPDATE CASCADE ON DELETE CASCADE,
foreign key(custID) REFERENCES Customer(custID) ON UPDATE CASCADE ON DELETE CASCADE,
foreign key(employeeID) REFERENCES Employee(employeeID) ON UPDATE CASCADE ON DELETE CASCADE);

Create table WilliamsSCW.Billing
(
billingID int(8) Not Null Primary Key,
billingDate TimeStamp Not Null,
billDeposit decimal(10,2),
billAmt int(8),
employeeID int(8),
custID int(8),
orderID int(20),
foreign key(employeeID) REFERENCES Employee(employeeID) ON UPDATE CASCADE ON DELETE CASCADE,
foreign key(custID) REFERENCES Customer(custID) ON UPDATE CASCADE ON DELETE CASCADE,
foreign key(orderID) REFERENCES Order1(orderID) ON UPDATE CASCADE ON DELETE CASCADE);

Create table WilliamsSCW.Media_Catalog
(
mediaID BIGINT(20) Not Null Primary Key,
mediaCatalog varchar(30),
mediaPrice decimal(10,2),
mediaDesc text,
mediaJob varchar(20),
mediaAmntStock int(10));

Create table WilliamsSCW.Reason
(
onholdID int(20) Not Null Primary Key,
onholdDesc varchar(100),
orderID int(20),
foreign key (orderID) REFERENCES Order1(orderID) ON UPDATE CASCADE ON DELETE CASCADE);

谢谢

Answer 1

您必须将 WilliamsSCW.Media_Catalog 的 DDL 放在 Order1 之前:

Create database WilliamsSCW;

Create table WilliamsSCW.Login
(
UserName varchar(50) Not Null Primary Key,
Password varchar(50) Not Null);

Create table WilliamsSCW.Employee
(
employeeID int(8) Not Null Primary Key,
employeeFName varchar(25),
employeeLName varchar(25));

Create table WilliamsSCW.Customer
(
custID int(8) Not Null Primary Key,
custFName varchar(25),
custLName varchar(25),
custAddress varchar(30),
custCity varchar(30),
custState varchar(30),
custZip int(5),
custPhone BIGINT(10),
custEmail text);

Create table WilliamsSCW.Media_Catalog
(
mediaID BIGINT(20) Not Null Primary Key,
mediaCatalog varchar(30),
mediaPrice decimal(10,2),
mediaDesc text,
mediaJob varchar(20),
mediaAmntStock int(10));

Create table WilliamsSCW.Order1  #order is a reserved key word in MySQL and can't be used
(
orderID int(20) Not Null Primary Key,
orderDesc varchar(30),
orderType varchar(20),
orderCost int(10),
orderQuantity int(6),
orderContent text,
orderStatus varchar(10),
orderDate TimeStamp,
orderShipDate Date,
orderDeposit decimal(10,2),
mediaID BIGINT(20),
custID int(8),
employeeID int(8),
foreign key(mediaID) REFERENCES Media_Catalog(mediaID) ON UPDATE CASCADE ON DELETE CASCADE,
foreign key(custID) REFERENCES Customer(custID) ON UPDATE CASCADE ON DELETE CASCADE,
foreign key(employeeID) REFERENCES Employee(employeeID) ON UPDATE CASCADE ON DELETE CASCADE);

Create table WilliamsSCW.Billing
(
billingID int(8) Not Null Primary Key,
billingDate TimeStamp Not Null,
billDeposit decimal(10,2),
billAmt int(8),
employeeID int(8),
custID int(8),
orderID int(20),
foreign key(employeeID) REFERENCES Employee(employeeID) ON UPDATE CASCADE ON DELETE CASCADE,
foreign key(custID) REFERENCES Customer(custID) ON UPDATE CASCADE ON DELETE CASCADE,
foreign key(orderID) REFERENCES Order1(orderID) ON UPDATE CASCADE ON DELETE CASCADE);

Create table WilliamsSCW.Reason
(
onholdID int(20) Not Null Primary Key,
onholdDesc varchar(100),
orderID int(20),
foreign key (orderID) REFERENCES Order1(orderID) ON UPDATE CASCADE ON DELETE CASCADE);