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.
Wednesday, March 28, 2012
Relationship between tables in different databases
Labels:
applications,
database,
databases,
managerwould,
microsoft,
mysql,
oracle,
own,
reference,
relationship,
server,
sql,
tables
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment