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.

Parent-Child Relationship Is Enforced by Triggers.

There are quite a few situations when triggers do not fire, such as:

·         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.

Parent-Child Relationship 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.