SQL Foreign Key Constraint doesn't allow relationship

Question

Hey guys,

I'll get straight to the point. I'm building a website on an existing database, which did not have relationships to keep it consistent. I'm now trying to add relationships to take care of this consitency problem.

simplified: I have a stock items table, locations table and a parts table.

the stock items table uses ID's that can be found in other tables (foreign keys). this table uses multiple columns as primary key (including locationid and partnr + some other) I succesfully added a relationship between the Location ID in the stock items table and the locations table. The problem occurs when I try to add the relationship between the stock items table and parts table:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint (in the parts table, partnumber table).

I checked the constraints and none were there. I tried looking for references to unexisting parts, but I could have written a bad query ...

can anyone help me figure out how to fix this?

Answer 1

Yup, it sounds like you wrote a bad query and there are references to non-existing parts.

SELECT partnumberid, (other fields)
FROM stockitems
WHERE partnumberid NOT IN (SELECT partnumberid FROM parts)

or

SELECT partnumberid, (otherfields)
FROM stockitems si
LEFT JOIN parts p ON si.partnumberid = p.partnumberid
WHERE p.partnumberid IS NULL