Making foreign key to unique index column in mysql

Question

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 ??

Madhur Bhaiya · Answer 1 · 2018-11-17T09:16:41.027

2

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

edited Nov 17 '18 at 09:16

answered Nov 17 '18 at 09:03

Madhur Bhaiya

28,155
10
49
57

it gives me this error `Error Code: 1822. Failed to add the foreign key constraint. Missing index in the referenced table ` – Tito Tito Nov 17 '18 at 09:04
@TitoTito You will need to add index on the parent table – Madhur Bhaiya Nov 17 '18 at 09:05
Please have a look at the edited version of the question – Tito Tito Nov 17 '18 at 09:05
So how to do that in mysql ? i am beginner :) – Tito Tito Nov 17 '18 at 09:06
it gives me `Error Code: 1215. Cannot add foreign key constraint ` – Tito Tito Nov 17 '18 at 09:11
@TitoTito then in that case, you have some rows in the `services` table which have some `PRODUCT_CODE` values, which do not exist in the `PRODUCTS` table. You need to fix those first. Check: https://stackoverflow.com/a/53099922/2469308 – Madhur Bhaiya Nov 17 '18 at 09:14

Making foreign key to unique index column in mysql

Products

1 Answers1