Relationships and constraints across databases

HI There,

what are the possible ways in which i can maintain relationships across instances of databases . i know relationships across DB's is bad approach , but i have to do this way.

i am using 开发者_运维知识库SQL SERVER 2005.

Thanks DEE

As far as I know it is impossible to do.

Your options are:

1. Set up replication between the datases so that the tables you want to define a relationship with are available in your local database. But that could get messy.
2. Create a UDF that does the checking and use that as a contraint.
3. Triggers.

However, this is such a bad idea that you really should re-evaluate whatever reasoning drove you to create multiple databases in the first place.

If you were looking for a business-ey solution, you probably want to look at having one or other of the databases be the clearing house for the "for business process purposes" existence/deletion of (a) records id'ed by keys (b) possibly-with-scope conditions expressed by particular rule data. This might or might not come under an IT heading instead and may or may not involve programming.

I am assuming of course in answering this question that the constraints management problem is substantial enough that people couldn't manage it on the back of an envelope; I am trying to answer the question as stated as generally as possible.

The programming source code types perhaps relevant to constraint management here are I would suggest...

• triggers and stored procedures in T/SQL

• validation in 3GL/4GL app code writing to databases

• validation in message-data-mapping and BRE business rule engine and other types of server systems

The constraint validation checks against either:

• a separate set of keys and rule data

• the main body of the data

It is possible to set up live or near-to-live links of the whole data set or a subset of it, but this rarely goes on in practice for four reasons:

(a) performance, overall system reliability, maintenance and cost issues of the system tend to get big very quickly; it has to be worth doing

(b) the integration issues of adding further systems are bigger

(c) there is the potential for people unaware of the necessity to maintain database links to go into a system and update data in a way which they think is helpful but which causes knock-on problems; so the system as a whole then requires stricter management processes which can be expensive

(d) delayed data throgh batch updates is often (though it is not unproblematic in itself!) sufficient for most business systems

From the system analyst's point of view, enough checkpoints need to be set up in the processes to validate the data, and enough synchronisation and/or integrity violation management steps need to occur.

EDIT

Replaced text "near-to-real time" with "delayed data".

EDIT

Replaced text "delayed data" with "delayed data through batch updates".

EDIT

Really replaced text "delayed data" with "delayed data through batch updates".