MySQL错误代码1215

Question

我有以下模式: WilliamsSCW和我一直在出错: 1215不能为Billing表添加外键约束。

请帮帮忙。我该怎么办？

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);