How can I request foreign keys that do not meet their restrictions?

Question

SQL Server 2005.

I am adding foreign key constraints to the application database, which supposedly did not need them. Naturally, the data became unreliable, and there are orphaned entries in the foreign key field.

Setup:
Two tables, TableUser and TableOrder. TableUser has the primary key 'UserID', and TableOrder has the foreign key 'UserID'.

How to find rows where TableOrder.UserID does not have a corresponding entry in TableUser.UserID?

For example, TableOrder.UserID is set to 250, but there is no corresponding Key for TableUser.UserID for 250.

Answer 1

Here is one way:

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

There are many different ways to write such a query.

Answer 2

Another 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 a where clause to browse and see the corresponding values ​​(if they exist) and see which ones do not match.

Answer 3

FK . FK PK, - , . , , . , , , , .

. , asshattery.

.