How do I query for foreign keys that don't match their constraints?

Question

SQL Server 2005.

I'm adding Foreign Key constraints to the database of an application that allegedly didn't need them. Naturally, the data has become unreliable and there are orphaned entries in the foreign key field.

Setup:
Two tables, TableUser and TableOrder. TableUser has Primary Key 'UserID', and TableOrder has Foreign Key 'UserID'.

How do I find the rows where TableOrder.UserID has no matching entry in TableUser.UserID?

For example, TableOrder.UserID has a value of 250, but there is no matching TableUser.UserID key for 250.

Answer 1

Here's one way:

select * from TableOrder where UserID not in (select UserID from TableUser);

There are many different ways to write this sort of query.

Answer 2

The other common approach is a left-outer join:

SELECT * FROM TableOrder o
LEFT OUTER JOIN TableUser u ON o.UserID = u.UserID
WHERE u.UserID is NULL

This query can also be useful without the where clause, to browse through and see the corresponding values (if they exist), and see which ones have no match.

Answer 3

ERRR, if there's a foreign key how can it not have a match? Do you actually have FKs hard coded in your SQL?

Answer 4

There were no FK Constraints in the tables to begin with. The were used like FK and PK but not coded -- the belief was that they were unnecessary overhead. So we have all the columns, but no coded constraints. When I went to put them in so that they would be enforced, I discovered that there were lots of violations.

Your question highlights the problem. They are not unnecessary overhead, they prevent people from general database asshattery.

Both Greg and Brad's answers helped me out.