我在最后一个表(ProductType)中遇到错误#1215 - Cannot add foreign key constraint
,有什么想法吗?
我得到的错误是:
BarCodeNum Varchar(12),
FoodType Varchar(15),
Constraint ProductType_PK Primary Key (VendorsLicense, BarCodeNum, FoodType),
Constraint Foreign Key (VendorsLicense) References VendorAndMerchandise(VendorsLicense),
Constraint Foreign Key (BarCodeNum) References Merchandise(BarCodeNum),
Constraint Foreign Key (FoodType) References Food(FoodType)
);
#1215-无法添加外键约束
Create table VendorAndMerchandise
(
FacultyID Numeric(5,0) Not Null,
VendorsLicense Numeric(10,0) Not Null,
timesOpen Varchar(9) Not Null,
VendorLocation Varchar(30) Not Null,
AverageIncome Integer Not Null,
Constraint VendorAndMerchandise_PK Primary Key (FacultyID, VendorsLicense)
);
Create table Food
(
FoodType Varchar(15) Not Null,
Price Integer Not Null,
Quanity Integer Not Null,
goodUntil Varchar(10) Not Null,
Constraint Food_PK Primary Key (FoodType)
);
Create table Merchandise
(
BarCodeNum Varchar(12) Not Null,
MechandiseType Varchar(30) Not Null,
Price Integer Not Null,
Quanity Integer Not Null,
Constraint merchandise_PK Primary Key (BarCodeNum)
);
Create table ProductType
(
VendorsLicense Numeric(10,0) Not Null,
BarCodeNum Varchar(12),
FoodType Varchar(15),
Constraint ProductType_PK Primary Key (VendorsLicense, BarCodeNum, FoodType),
Constraint Foreign Key (VendorsLicense) References VendorAndMerchandise(VendorsLicense),
Constraint Foreign Key (BarCodeNum) References Merchandise(BarCodeNum),
Constraint Foreign Key (FoodType) References Food(FoodType)
);
只需将VendorAndMerchance表中主键列的顺序更改为
Create table VendorAndMerchandise
(
FacultyID Numeric(5,0) Not Null,
VendorsLicense Numeric(10,0) Not Null,
timesOpen Varchar(9) Not Null,
VendorLocation Varchar(30) Not Null,
AverageIncome Integer Not Null,
Constraint VendorAndMerchandise_PK Primary Key (VendorsLicense, FacultyID)
);
有问题的行是:
Constraint Foreign Key (VendorsLicense) References VendorAndMerchandise(VendorsLicense),
MySQL需要外键和引用键上的索引,以便外键检查可以很快,并且不需要表扫描。在引用表时,必须有一个索引,其中外键列按相同顺序列为第一列。这样一个如果没有,则会自动在引用表上创建索引存在如果您创建可用于强制执行外键约束的另一个索引。index_name(如果给定)将按前面所述使用。
InnoDB允许外键引用列。但是,在被引用的表中,必须有一个索引其中引用的列列列为相同的顺序。
http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
您应该添加一个以被引用列为第一列的索引,或者使用父关键字中的每一列进行引用。
要么(UNIQUE可以是INDEX,但引用不明确的列不是一个好主意)
Create table VendorAndMerchandise
(
FacultyID Numeric(5,0) Not Null,
VendorsLicense Numeric(10,0) Not Null,
timesOpen Varchar(9) Not Null,
VendorLocation Varchar(30) Not Null,
AverageIncome Integer Not Null,
Constraint VendorAndMerchandise_PK Primary Key (FacultyID, VendorsLicense),
CONSTRAINT UX_VendorsLicense unique(VendorsLicense)
) ENGINE=InnoDB;
或
Create table ProductType
(
FacultyID Numeric(5,0) Not Null,
VendorsLicense Numeric(10,0) Not Null,
BarCodeNum Varchar(12),
FoodType Varchar(15),
Constraint ProductType_PK Primary Key (VendorsLicense, BarCodeNum, FoodType),
Constraint Foreign Key (FacultyID, VendorsLicense) References VendorAndMerchandise(FacultyID, VendorsLicense),
Constraint Foreign Key (BarCodeNum) References Merchandise(BarCodeNum),
Constraint Foreign Key (FoodType) References Food(FoodType)
);