Having Foreign Key relations between two different columns in two different databases

Question

I have two databases A and B. A has table tableA with columnA similarily B has tableB with coulmnB. Can I have a Primary , Foreign Key relationship between these two columns. Both databases will be in the same Sql Server 2008 R2 instance.

I am using Sql Server 2008 R2 Express Edition.

Also if this is not available in express edition then is it available in other editions such as enterprise

Answer 1

Its not possible, but you may implement custom mechanism by triggers.

The problem is - you never can say that your backups are consistent.

Since referential integrity implemented with FOREIGN KEY constraint guarantees that all your data are valid after the transaction ends and your backups always be consistent.

With different databases and trigger-based ref. integrity you never can say that both databases backed up simultaneously and in consistent state.

Answer 2

In your scenario, you have 2 databases - Creating FK between databases isn't possible (even within same SQL server instance).

Even if you had these tables within one database, it would lead to circular refference.

Does that answer your question?

Answer 3

As per the comments. No, unfortunately you simply can't.

Could you have the table sin the same database but different schemas? That would allow the foreign key relationship.