Reputation: 21
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
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