views:

465

answers:

4

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.

+7  A: 

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.

Greg Hewgill
+3  A: 

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.

BradC
A: 

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

Andrew G. Johnson
He means a table with fields that are treated by the application as foreign key, but were never enforced by the database itself.
BradC
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).
Jason Short
A: 

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.

Dave
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...
Jason Short