Creating relationships between different databases

Hello guys,
Does anybody know how can i create a relationship between tables in different databases? Does SQL Server supports this kind of operation?
The main problem is that i have some data that is shared between different databases and i'd like to have some kind of referencial integrity in the data that is stored on the separated databases by using the same shared tables. I could use triggers for do
ing that but this will decrease the performance of my databases dramatically.
Thanks for the help,
Paulo R.
Triggers work, but as you point out there are performance problems.
You can't do cross database constraints or you will get a message like:
Server: Msg 1763, Level 16, State 1, Line 1
Cross-database foreign key references are not supported. Foreign key
'master.dbo.jobs'.
I'm not sure what to suggest other than triggers.
"Paulo" <paulo@.specforeclosure.com> wrote in message
news:A4BC16DB-C2BB-4EC9-88C4-284F0BA4E957@.microsoft.com...
> Hello guys,
> Does anybody know how can i create a relationship between tables in
different databases? Does SQL Server supports this kind of operation?
> The main problem is that i have some data that is shared between different
databases and i'd like to have some kind of referencial integrity in the
data that is stored on the separated databases by using the same shared
tables. I could use triggers for doing that but this will decrease the
performance of my databases dramatically.
> Thanks for the help,
> Paulo R.
|||Paulo,
if you really want this functionality, then you could enforce the constraints in your stored procedures, assuming database access is via sps, or use triggers, or use filegroups. This last option is unconventional and in my opinion not ideal, but it is pos
sible to put all your tables into one database and declare constraints there. What were databases now become filegroups. You can back up individual filegroups like you back up databases. I know one financial services institution that uses this methodology
. However, this is limiting - there is no posibility of a point-in-time restore and you need to be able to get hold of the last transaction log if you want to do a full restore. However, if you only use database backups, then this is an option to consider