Error #1215 - Cannot add foreign key constraint - MySQL

Vote count:

0

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

Create table Zoo
(
 LocationID     Numeric(5,0)    Not Null,
 Zname          Varchar(15)     Not Null,
 Zaddress       Varchar(30)     Not Null,

 CONSTRAINT Zoo_PK PRIMARY KEY (LocationID)
);

Create table Visitor
(
 vNumber        Integer         Not Null,
 visitorName    Varchar(15),
 LocationID     Numeric(5,0)    Not Null,

 CONSTRAINT Visitor_PK PRIMARY KEY (vNumber),
 CONSTRAINT Visitor_FK FOREIGN KEY (LocationID)REFERENCES Zoo(LocationID)
);

Create table Habitat
(
 HabitatID      Numeric(5,0)    Not Null,
 habitatName    Varchar(15)     Not Null,
 hLocation      varchar(30)     Not Null,
 LocationID     Numeric(5,0)    Not Null,

 Constraint Habitat_PK Primary Key (HabitatID),
 Constraint Habitat_PK Foreign Key (LocationID) References Zoo(LocationID)
);

Create table Animal
(
 AnimalID       Numeric(5,0)     Not Null,
 AName          Varchar(15)     Not Null,
 Classification Varchar(30),
 HabitatID      Numeric(5,0)    Not Null,

 Constraint Animal_PK Primary Key (AnimalID),
 Constraint Animal_FK Foreign Key (HabitatID) References Habitat(HabitatID)
);

Create table FoodAndMeds
(
 BarCode        Numeric(12,0)    Not Null,
 SupplyUse      Varchar(100)     Not Null,

 Constraint FoodAndMeds_PK Primary Key (BarCode)
);

Create table Supplies
(
 AnimalID       Numeric(5,0)     Not Null,
 BarCode        Numeric(12,0)    Not Null,

 Constraint Supplies_PK Primary Key (AnimalID, BarCode),
 Constraint Supplies_FK1 Foreign Key (AnimalID) References Animal(AnimalID),
 Constraint Supplies_FK2 Foreign Key (BarCode) References   FoodAndMeds(BarCode)
);



Create table Employee
(
 FacultyID      Numeric(5,0)     Not Null,
 SSN            Varchar(11)     Not Null,
 EmployeeName   Varchar(30)     Not Null,
 Gender         Varchar(6),
 Address        Varchar(50)     Not Null,
 PhoneNumber    Varchar(12),
 LocationID     Numeric(5,0)     Not Null,
 Constraint Employee_PK Primary Key (FacultyID),
 Constraint Employee_FK Foreign Key (LocationID) References Zoo(LocationID)
);

Create table StaffMember
(
 FacultyID        Numeric(5,0)      Not Null,
 StaffPosition  Varchar(30)       Not Null,
 Salary         Integer           Not Null,
 WorkLocation   Varchar(30),

 Constraint StaffMember Primary Key(FacultyID)
);

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

asked 35 secs ago

Jake Hall

1

Error #1215 - Cannot add foreign key constraint - MySQL