CODEWITHSUNDEEP
mysqldatabase
Jake Hall
Jake Hall

Reputation: 13

Error #1215 - Cannot add foreign key constraint - MySQL

I am running into the error #1215 - Cannot add foreign key constraint, with my last table (ProductType), any ideas why it is not working?

the error I am getting is:

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 - Cannot add foreign key constraint 

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

Upvotes: 1

Views: 124

Answers (2)

gaurav tiwari
gaurav tiwari

Reputation: 11

just change the order of primary key columns in VendorAndMerchandise table as below

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

Upvotes: 1

Pred
Pred

Reputation: 9042

The problematic line is this:

Constraint Foreign Key (VendorsLicense) References VendorAndMerchandise(VendorsLicense),

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

You should either add an index with the referenced column as the first one or use each columns from the parent key to reference.

Either (The UNIQUE could be INDEX, but it is not a good idea to reference ambiguous columns)

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;

Or

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

Upvotes: 0

Related Questions