CODEWITHSUNDEEP
mysqldatabase
Tito Tito
Tito Tito

Reputation: 21

make a foreign key to unique index in mysql

products

CREATE TABLE if NOT EXISTS `PRODUCTS` (
   `ID` INT unsigned NOT NULL AUTO_INCREMENT,
   `COMPANY_ID` INT(10) unsigned NOT NULL,
   `PRODUCT_CODE` VARCHAR(5) NOT NULL,
   `PRODUCT_NAME` VARCHAR(15) NOT NULL,
   PRIMARY KEY (`ID`),
   UNIQUE INDEX UNIQUE_COMAPNY_UNIT_CODE (`COMPANY_ID`, `PRODUCT_CODE`)
)

Services

ALTER TABLE services
ADD COLUMN `PRODUCT_CODE` VARCHAR(5),
ADD FOREIGN KEY (`PRODUCT_CODE`) REFERENCES PRODUCTS(`PRODUCT_CODE`) ON DELETE CASCADE ON UPDATE CASCADE;

but it always gives me this error when trying to make the upper foreign key missing index in the references table products ... any help how to make a product_code in services table refrences to product_code in products table ?

Upvotes: 0

Views: 28

Answers (1)

Uueerdo
Uueerdo

Reputation: 15961

Foreign keys must reference indexes that start with the fields referenced, in the order referenced. To reference PRODUCT_CODE, you must have an index on (or starting with) PRODUCT_CODE.

Also, typically, you reference the PK of the table; if for no other reason than it minimizes the situations where cascades are needed. A product code is more likely to change than an auto incremented row identifier.

Upvotes: 1

Related Questions