Finding orphaned records in SQL

In general relationships should always be created between a table's Primary Key and the associated Foreign Keys in other tables. However sometimes those relationships might not already exist in the database and it must be added later on. This can sometimes fail due to the constraint being unenforceable on the existing data (e.g. because certain database records have already been deleted and the IDs persist in the Foreign Key's table).

To solve this problem you can write one of the following queries to discover the records at fault:

SELECT * FROM FK_table f LEFT OUTER JOIN PK_table p ON p.ID = f.ID
WHERE p.ID IS NULL

SELECT * FROM FK_table f
WHERE NOT EXISTS (SELECT p.ID FROM PK_table p WHERE f.ID = p.ID)

SELECT * FROM FK_table f
WHERE f.ID NOT IN (SELECT p.ID FROM PK_table p)

This final T-SQL method is actually very useful as the equivalent DELETE syntax also works. Therefore to then delete these faulty records you can run this query:

DELETE FROM FK_table
WHERE ID NOT IN (SELECT ID FROM PK_table)

It should also be noted that sometimes Constraints can get dropped automatically when making changes to a table. So any table changes should be followed by a check to make sure all Relationships are still in place.