CODEWITHSUNDEEP
mysql
Tito Tito
Tito Tito

Reputation: 21

Making foreign key to unique index column 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,
   `UNIT_TYPE` VARCHAR(1) NULL,
   UNIQUE INDEX UNIQUE_COMAPNY_PRODUCT_CODE (`COMPANY_ID`, `PRODUCT_CODE`),
   CONSTRAINT `PRODUCT_COMPANY_ID_FK` FOREIGN KEY (`COMPANY_ID`) REFERENCES `companies` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
)

and i want to make a foreign key to the upper 'PRODUCT_CODE' ...... here is my code

ALTER TABLE services
ADD COLUMN `PRODUCT_CODE` VARCHAR(5),
// ADD foreign `PRODUCT_CODE` that refrences to `PRODUCT_CODE` in PRODUCTS table

So how to do the commented line above in mysql ??

Upvotes: 0

Views: 1824

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

The column referenced in a foreign key may be either a Primary Key or Unique. You need to first add an index on PRODUCT_CODE. I have taken it as UNIQUE

ALTER TABLE PRODUCTS ADD UNIQUE INDEX (PRODUCT_CODE);

Then, you can use ALTER TABLE .. ADD FOREIGN KEY syntax:

ALTER TABLE services
ADD FOREIGN KEY (PRODUCT_CODE) REFERENCES PRODUCTS(PRODUCT_CODE);

Combining this to your existing ALTER TABLE query, a single query would look as follows:

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

If you already have some data in the services table, which has certain PRODUCT_CODE value, which do not exist in the PRODUCTS table. You will get the following error:

Error Code: 1215. Cannot add foreign key constraint

In that case, you will need to fix the data in the tables. You may check this answer for the tips: https://stackoverflow.com/a/53099922/2469308

Upvotes: 2

Related Questions