CODEWITHSUNDEEP
sql-serverdatabaserelationship
string QNA
string QNA

Reputation: 3200

Relationships between tables from different databases

Is it possible to define relationships between tables in different databases in SQL server 2008? And can you recommend an online tutorial for studying it? (I prefer ASP.NET, C#)

Upvotes: 6

Views: 18115

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21756

Yes you can but NOT using FOREIGN KEYS:

  1. You can use specific stored procs, which checks the consistency - in this case you have to make the user to use only these procedures for all the CRUD operations in both DBS

  2. Triggers, which will check the same

  3. All of the above have to run within properly isolated transaction to be sure, that your "just checked" values will not be deleted in a moment

Upvotes: 7

gbn
gbn

Reputation: 432210

No, you can't have foreign keys between databases.

Data integrity is within a single database only. If you need transactional consistency across databases then you should use a single database. The main issue is backups/restores: you will end up with broken data after a restore because your backups are not consistent.

A recent blog article "One Database or Ten?" explains in more details

Saying that, you can use triggers if you need this and are prepared to have broken data

Upvotes: 21

Related Questions