March 21, 2006 at 8:04 am
Hello,
I wonder if anybody would have a suggestion on dealing with this situation. In our organisation we have one central database that holds the majority of our data. There is a table in another database that we now support that needs to use a table in the central database as a lookup. Is it possible to create foreign key links between tables in 2 separate databases, and if so, is it advisable ? If not, what can I do ? (I dont want to start replicating the table between the databases). Any suggestions gratefully accepted.
IDBOracle
March 21, 2006 at 8:31 am
Cross database foreign keys are not supported in SQL Server. For query purposes, however, you can create linked servers (same as db links in Oracle) and then query across the linked server and filter off/join/query the remote table.
March 21, 2006 at 10:05 am
Maybe you could create a trigger that would perform the integrity checks that you need on insert, update etc.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 22, 2006 at 7:55 am
If you're on 2005, you could move both databases into one and separate the tables with schemas, and then use foreign keys for integrity. You could do the same with 2000, using either a naming convention to separate the tables or different table owners.
Triggers would work to, but in can get tricky if you're modifying data in the parent lookup table.
Hope that helps.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply