SQL External Key Constraint does not allow relationship
I'll go straight to the point. I am creating a website on an existing database that had no relationship to keep it consistent. Now I am trying to add a relationship to take care of this consent issue.
simplified: I have a stock table, a locations table and a parts table.
the inventory item table uses an identifier that can be found in other tables (foreign keys). this table uses multiple columns as primary key (including locationid and partnr + some others) I have successfully added a relationship between the location id in the inventory item table and the locations table. The problem comes when I try to add a relationship between the table of elements and the table of parts:
The ALTER TABLE attribute conflicts with the FOREIGN KEY constraint (in table of parts, table of part numbers).
I checked the limits and no one was there. I tried to find links to unused parts, but I could write a bad query ...
can anyone help me figure out how to fix this?
a source to share
Yes, it looks like you wrote a bad query and there are links to non-existent parts.
SELECT partnumberid, (other fields)
FROM stockitems
WHERE partnumberid NOT IN (SELECT partnumberid FROM parts)
or
SELECT partnumberid, (other fields)
FROM stockitems si
LEFT JOIN part p ON si.partnumberid = p.partnumberid
WHERE p.partnumberid IS NULL
a source to share