ERROR 1005 (HY000) mySql, on small database

Question

Hey so i just started using mysql and made a small database, but then i tried to alter some tables to impliment some foreign keys and I got the error: ERROR 1005 (HY000): Can't create table etron3.purchase (errno: 150 "Foreign key constraint is incorrectly formed").

I know its cause when altering TillNo into a foreing key of TillN, but i cant really see what the problem is.

create table BRANCH(BranchNo INT, BranchAddr VARCHAR(25), BranchTell INT, PRIMARY KEY(BranchNo));


/*Create table CUSTOMER*/
create table CUSTOMER(CustNum INT, CustTell INT, CustFname VARCHAR(15), CustLname VARCHAR(15), CustCountry VARCHAR(20),
CustTown VARCHAR(20), CustStreet VARCHAR(20), CustPostCode CHAR(7), CustDob DATE, CustEmail VARCHAR(45), PRIMARY KEY(CustNum, CustTell));


/*Create table ASSISTANT*/
create table ASSISTANT(SalesPNo INT,TillN INT , SalePFname VARCHAR(20), SalePLname VARCHAR(20), PRIMARY KEY(SalesPNo, TillN));


/*Create table PURCHASE*/
create table PURCHASE(TDateTime DATETIME, TillNo INT, CustNo INT, AssistantNo INT, ProdCode INT, Qty INT, PRIMARY KEY(TDateTime));


/*Create table SUPPLIER*/
create table SUPPLIER(SupCnum INT, SupCname VARCHAR(15), SupFname VARCHAR(15), SupLname VARCHAR(15), SupEmail VARCHAR(30), ProdNum INT, PRIMARY KEY(SupCnum));


/*Create table PRODUCT*/
create table PRODUCT(ProductCode INT, PType VARCHAR(20), Price INT, OfferPrice INT, PRIMARY KEY(ProductCode, PType));


/*Create table PRODTYPE*/
create table PRODTYPE(ProdGroup VARCHAR(20), PCode INT, PRIMARY KEY(ProdGroup));



/*Altering tables, adding foreign keys, so that I don't get any errors*/



/*Add primary and foreign keys to PURCHASE*/

ALTER TABLE PURCHASE
ADD FOREIGN KEY(AssistantNo) REFERENCES ASSISTANT(SalesPNo);

ALTER TABLE PURCHASE
ADD FOREIGN KEY(ProdCode) REFERENCES PRODUCT(ProductCode);

ALTER TABLE PURCHASE
ADD FOREIGN KEY(CustNo) REFERENCES CUSTOMER(CustNum);

ALTER TABLE PURCHASE
ADD FOREIGN KEY (TillNo) REFERENCES ASSISTANT(TillN);

/*Add foreign key to SUPPLIER*/
ALTER TABLE SUPPLIER
ADD FOREIGN KEY(ProdNum) REFERENCES PRODUCT (ProductCode);


/*Add primary and foreign keys to PRODTYPE*/

ALTER TABLE PRODTYPE
ADD FOREIGN KEY(PCode) REFERENCES PRODUCT(ProductCode);

score 0 · Accepted Answer · answered Apr 07 '20 at 23:10

Consider this part of your code:

ALTER TABLE PURCHASE
ADD FOREIGN KEY(AssistantNo) REFERENCES ASSISTANT(SalesPNo);

ALTER TABLE PURCHASE
ADD FOREIGN KEY (TillNo) REFERENCES ASSISTANT(TillN);

The primary key of the ASSISTANT table is (SalesPNo, TillN).

Technically, you cannot have a foreign key point at the second column only of the other table's primary key - MySQL wants the referred column to be indexed, or to appear first in a compound index.

Functionnaly, you most probably want a compound foreign key rather than two individual foreign keys, so:

ALTER TABLE PURCHASE
ADD FOREIGN KEY (AssistantNo, TillNo) REFERENCES ASSISTANT(SalesPNo, TillN);

With this change, your script runs succesfully in this db fiddle.

Recommended reading: MySQL Foreign Keys - Constraints and Restrictions.

ERROR 1005 (HY000) mySql, on small database

1 Answers1