MySQL错误1215外键问题

Question

试图找出为什么我的代码会出现1215错误。我搜索了所有的帖子，看了一大堆，并尝试了对其他人有效的“解决方案”，但我似乎无法将这些修复应用到我的代码中。代码如下(编辑，把我的整个代码放进去)：

CREATE DATABASE IF NOT EXISTS ERUS30114465;

USE ERUS30114465;

CREATE TABLE IF NOT EXISTS Employee
(
EmployeeID int(15),
PRIMARY KEY(EmployeeID),
FirstName varchar(40),
LastName varchar(40),
DateStarted date,
TaxNum int(9),
StoreID int(15)
)ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS Store
(
StoreID int(15),
PRIMARY KEY(StoreID),
Name varchar(40),
Email varchar(60),
Phone int(10),
Fax int(20),
StoreManager int(15),
SupervisingStoreID int(15),
FOREIGN KEY(SupervisingStoreID) REFERENCES Store(StoreID)   
)ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS Artist
(
ArtistID int(25),
PRIMARY KEY(ArtistID),
FirstName varchar(40),
LastName varchar(40),
DOB date,
Website varchar(255)
)ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS Category
(
CategoryID char(1) PRIMARY KEY,
Description varchar(255)
)ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS CD
(
CDNumber int(8),
CategoryID char(1),
Length int(6),
NumberOfTracks int(8),
ReleaseDate date,
ReorderLevel int(8), 
Title varchar (255),
PRIMARY KEY(CDNumber),
FOREIGN KEY(CategoryID) REFERENCES Category(CategoryID)
)ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS CDArtist
(
ArtistID int(25),
CDNumber int(8),    
FOREIGN KEY(ArtistID) REFERENCES Artist(ArtistID),
FOREIGN KEY(CDNumber) REFERENCES CD(CDNumber)
)ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS Transactions
(
TransactionID int(25),
TransDate date,
StoreNumber int(15),   
PRIMARY KEY(TransactionID),
FOREIGN KEY(StoreNumber) REFERENCES Store(StoreID)
)ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS TransactionDetails
(
CDNumber int(8),
TransactionID int(25),
Quantity int(8),
Discount int(2),
FOREIGN KEY (CDNumber) REFERENCES CD(CDNumber),
FOREIGN KEY (TransactionID) REFERENCES Transactions (TransactionID)
)ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS Inventory
(
CDNumber int(8),
StoreID int(15),
QuantityOnHand int(8),
QuantityOnOrder int(8),
FOREIGN KEY (CDNumber) REFERENCES CD(CDNumber),
FOREIGN KEY (StoreID) REFERENCES Store(StoreID)
)ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS Address
(
Tid char(1),
PRIMARY KEY(Tid),
AddressID int(30),
AddressType varchar(60),
FOREIGN KEY(AddressType) REFERENCES AddressType(AddressType),
Street varchar(255),
City varchar(255),
State varchar(255)
)ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS AddressType
(
AddressType varchar(60), 
PRIMARY KEY (AddressType),
Description varchar(255)
)ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS Customer
(
CustomerID int(25),
PRIMARY KEY(CustomerID),
FirstName varchar(60),
LastName varchar(60), 
Phone int(10)
)ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS CDPrice
(
EffectiveFrom date,
Price int(5),
Reason varchar(255),
CDNumber int(8),
PRIMARY KEY(EffectiveFrom),
FOREIGN KEY(CDNumber) REFERENCES CD(CDNumber)
)ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS CustomerOrder
(
CustomerOrderID int(25),
OrderDate date,
StoreID int(15),
CustomerID int(25),
PRIMARY KEY(CustomerOrderID), 
FOREIGN KEY(StoreID) REFERENCES Store(StoreID),
FOREIGN KEY(CustomerID) REFERENCES Customer(CustomerID)
)ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS OrderDetails
(
CDNumber int(8),
CustomerOrderID int(25),
DateArrived date,
DatePickedUp date,
Quantity int(8),
FOREIGN KEY(CDNumber) REFERENCES CD(CDNumber),
FOREIGN KEY(CustomerOrderID) REFERENCES CustomerOrder(CustomerOrderID)
)ENGINE = InnoDB;

"#1215 - Cannot add foreign key constraint "

所述错误(现在)与最后一个表OrderDetails相关

如果它盯着我的脸，我看不到它-_-我已经连续工作了大约16个小时……

任何非常感谢的帮助:D

Answer 1

基本上，DATE是MYSQL的保留关键字。

如果你想使用，你可以尝试使用反引号，如下所示

CREATE TABLE IF NOT EXISTS `Transaction`
(
TransactionID int(25),
`Date` date,
StoreNumber int(15),   
PRIMARY KEY(TransactionID),
FOREIGN KEY(StoreNumber) REFERENCES Store(StoreID)
)ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS TransactionDetails
(
CDNumber int(8),
TransactionID int(25),
Quantity int(8),
Discount int(2),
FOREIGN KEY (CDNumber) REFERENCES CD(CDNumber),
FOREIGN KEY (TransactionID) REFERENCES `Transaction`(TransactionID)
)ENGINE = InnoDB;

Answer 2

事务不是保留字，至少在5.5 http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html中

我在transaction表中删除了对store表的外键引用，它worked.Are您确定没有忘记创建store表吗?或者，如果存在，请确保两列的类型和大小相同，并且两个表具有相同的引擎

http://sqlfiddle.com/#!2/6c82c