Relationships between tables from different databases

Question

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#)

Answer 1

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

Answer 2

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