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

Here's an article on how to use the SSIS Import / Export wizard:

http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm

The easiest way to do this is just to export one database (I'd use the smallest of the two) to whatever format is the most convenient for you, and then import it into the other. As long as the table names are all different, this shouldn't present any problem.

Answer 2

Triggers can be written to enforce referential integrity against different databases.

Answer 3

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.