CODEWITHSUNDEEP
sqlsql-server-2008r2-express
bleu
bleu

Reputation: 87

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

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

Upvotes: 2

Views: 618

Answers (3)

Oleg Dok
Oleg Dok

Reputation: 21766

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.

Upvotes: 3

MatBailie
MatBailie

Reputation: 86715

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.

Upvotes: 1

Sebastian Siek
Sebastian Siek

Reputation: 2075

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?

Upvotes: 1

Related Questions