我有这个代码来创建一些表:
CREATE TABLE Appelations (
No int AUTO_INCREMENT,
Appelation varchar(35),
County varchar(20),
State varchar(15),
Area varchar(25),
IsAVA varchar(3),
PRIMARY KEY (No)
);
CREATE TABLE Grapes (
ID int AUTO_INCREMENT,
Grape varchar(25),
Color varchar(10),
PRIMARY KEY (ID)
);
CREATE TABLE Wine (
No int AUTO_INCREMENT,
Grape varchar(25),
Winery varchar(40),
Appelation varchar(35),
Name varchar(40),
Year year,
Price int,
Score int,
Cases int,
PRIMARY KEY (No),
FOREIGN KEY (Grape) REFERENCES Grapes(Grape),
FOREIGN KEY (Appelation) REFERENCES Appelations(Appelation)
);
但是当我运行它时,我收到此错误:
ERROR 1005(HY000):无法创建表'db.Wine'(错误号:150)
有谁知道如何解决这一问题?谢谢 .
2 回答
你need indexes on your foreign keys:"MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan."
示例:http://sqlfiddle.com/#!9/d228b8
这是因为表中的
FOREIGH KEY
列引用的列既不是主键也不是索引(在父表中) . 您可以更改类型并使它们分别指向Grapes
和Appelations
表的ID
和No
列,例如:这是 SQL Fiddle .