0

I am trying to add a foreign key to a table that has a composite key. I was able to add the foreign key for the first column but not the second.

I have searched stackoverflow, reading all of the similar questions. I've searched the internet. I have checked the parent tables against the child table and the column definitions appear to be the same. I've checked the spelling of the column names. I have used another alter statement that worked for another foreign key. I'm at a loss.

Table: orderdetails

Columns:
OrderID int(11) PK 
ProductID int(11) PK

Table: products

Columns:
productid int(11) AI PK

ALTER TABLE orderdetails
     ADD CONSTRAINT fk_od_prodid
     FOREIGN KEY (ProductID) REFERENCES products(productid);

I expected to create the foreign key but got error code 1452 instead.

Will B.
  • 17,883
  • 4
  • 67
  • 69
Gail
  • 1
  • 1
  • See if this answer helps: https://stackoverflow.com/questions/21659691 – zedfoxus Apr 24 '19 at 02:11
  • Possible duplicate of [Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails](https://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa) – Will B. Apr 24 '19 at 02:54

2 Answers2

0

This typically occurs when one of the rows exists already in the child table orderdetails, that is not also in the parent table products.

You can ignore the error and force it temporarily by using

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE orderdetails
     ADD CONSTRAINT fk_od_prodid
     FOREIGN KEY (ProductID) REFERENCES products(productid);
SET FOREIGN_KEY_CHECKS=1;

Alternatively to find the offending records use

SELECT o.* 
FROM orderdetails AS o 
LEFT JOIN products AS p 
ON p.productid = o.ProductID 
WHERE p.productid IS NULL;

And to remove the offending records
(only affects the orderdetails table, leaving products unmodified.)

DELETE o
FROM orderdetails AS o
LEFT JOIN products AS p
ON p.productid = o.ProductID
WHERE p.productid IS NULL;

After removing the offending records, your ALTER statement should work correctly.

Will B.
  • 17,883
  • 4
  • 67
  • 69
0

I finally figured out what was wrong. there were two values in the child table that were not in the parent table. I deleted those records and my error went away.

Gail
  • 1
  • 1