Relationship between tables in different databases

We have several applications, each with its own database. We also have a
"Reference" database that is used by all of the applications. My manager
would like me to create some relationships between tables in Reference and
one of the other databases. I can't do this in either the Database Diagram
or in Table Designer, because both only show me tables in one database at a
time. Is there another way to do it?
Much obliged.
Not possible to create foreign key constraints across databases. You will
have to use triggers to enforce this yourself.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Geoff" <Geoff.Pennington.ctr@.NOSPAMwhs.mil> wrote in message
news:OTsKLMYfFHA.3936@.TK2MSFTNGP14.phx.gbl...
We have several applications, each with its own database. We also have a
"Reference" database that is used by all of the applications. My manager
would like me to create some relationships between tables in Reference and
one of the other databases. I can't do this in either the Database Diagram
or in Table Designer, because both only show me tables in one database at a
time. Is there another way to do it?
Much obliged.
|||"Geoff" <Geoff.Pennington.ctr@.NOSPAMwhs.mil> wrote in message
news:OTsKLMYfFHA.3936@.TK2MSFTNGP14.phx.gbl...
> We also have a "Reference" database that is used by all of the
> applications. My manager would like me to create some relationships
> between tables in Reference and one of the other databases.
In my experience I've found this is generally a bad idea. I don't even
really think this is the intent of the technology anyway. This also
invariably means that at some point you'll have cross-database ownership
chaining in addition to other strange hack such as creating custom triggers
to enforce constraints (as someone else has stated). Also, you'll find
yourself writing a lot more dynamic SQL to get anything done.
sql