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.

Frans

Once you've found them, what do you want to do with them? E.g., delete them? – Footstone

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

He means a table with fields that are treated by the application as foreign key, but were never enforced by the database itself. – Bicephalous

Or the FK constraint was added after the fact (SQL Server does not apply them to existing rows by default), or if someone turned off FK validation to force something and screwed up the database as a result (seen that too many times). – Sidwohl

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.

Sindee

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.

Bicephalous

Answer 3

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.

Frans

It is a common misconception I think. Lots of people think that should be done in the middle tier for business logic. But the FK is a fundamental part of any relational database. If the database can't do it well then there are other problems... – Sidwohl