I am going to discuss several potential problems with referential
integrity, because they keep coming up in the newsgroups.
Parent and Child Tables
Are in Different Databases.
Although you cannot use a foreign key in this situation,
there are workarounds – you can use either triggers or UDFs wrapped in check
constraints. Either way, your data integrity is not completely watertight: if
the database with your parent table crashes and you restore it from a backup,
you may easily end up with orphans.
Is Enforced by Triggers.
There are quite a few situations when triggers do not fire,
A table is dropped.
A table is truncated.
Settings for nested and/or recursive triggers
prevent a trigger from firing.
Also a trigger may be just incorrect. Either way, you may
end up with orphans in your database.
Is Enforced by UDFs Wrapped in Check Constraints.
Such “constraints” do not prevent from deleting parent rows.
Again, you may end up with orphans in your database.
In most cases the easiest and simplest way to enforce a
parent-child relationship is to use a built-in tool – do not reinvent the wheel,
have both tables in one database and use a foreign key.