Sergiy (5/6/2013)
Try to run queries like this:
select * from FK_Table FWHERE NOT EXISTS (
select * from PK_Table P
where P.PK_Column1 = F.FK_Column1 and P.PK_Column2 = F.FK_Column2
It should give you the entries (so called "orhaned records") in "FK_Table" which prevent you from creating the FK.
Inspect them and decide what you can do about it:
- delete orhaned records from FK_Table;
- add missing kes to PK_Table;
- reconsider the FK.
modrhafi.sk, Sergiy's code will help you find and solve problems in the data, but I think you might need a clear explanation of the problem first! You have rows in your tables with FK constraints where the key values do not exist in the referenced tables. When you attempt to create the FK constraint, the effort fails because SQL Server can't create a FK constraint that will be violated by data already in the tables.
An example of your situation:
TableB:
BID someColumn
1 xyz
2 abc
3 mno
TableA:
AID someOtherColumn
1 987654
2 654321
In this situation, you cannot add a FK constraint on TableB.BID referencing TableA.AID because there is a value, 3, in TableB.BID that does not exist in TableA.AID.
Sergiy's code will help you find the rows like these that are hindering your efforts to add FK constraints. Since those rows exist, though, you may need to review whether you have correctly derived the relations - do the FK constraints that you are trying to create really reflect business requirements?
Jason Wolfkill