sql - foreign keys on table from different database

Question

I have two databases in SQL Server and i have a common table for both the databases an important big table which holds the foreign keys to other tables. The problem is the Table is in DatabaseA, and I need to refer foreign keys to this table from DatabaseB.

I know SQL doesn't support cross database referential integrity so what's the best way to achieve this? I am thinking of combining two databases and make into single database - it wouldn't matter aside from the increase in complexity.

Any suggestions?

Answer 1

I would avoid doing this if I could - can you just keep both tables in one datbase and use an FK?

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.