You cannot use declarative referential integrity. That is, I cannotQuote:> In a data modelling class recently, it was pointed out to me that the RDBMS
> does not support referential integrity between separate physical databases.
> E.g. if customers and orders resided in separate physical databases on the
> same server RI could not protect a customer from being deleted who has orders
> in the other database. (This is a hypothetical example, but we have a
> similar situation.)
declare a primary key in table db1..customer, and a reference constraint
or foreign key constraint on table db2..orders. I can, however, roll my
own RI constraints using triggers.
This scenario is, however, rarely done. To answer your next question...
Data that should have DRI constraints (data dependencies) between themQuote:> It sounds like, ideally, an enterprise database should then be in one
> physical database.
belong in the same database.
You can write triggers to fix your current situation. You could use viewsQuote:> Can anyone confirm this last notion for me? Also, given that we do currently
> have separate physical databases, does anyone have any ideas about how to
> back out of that situation? I thought about moving the physical data to the
> primary database and replacing the individual tables with views that point to
> the actual data in the primary database.
the way you suggest, but at a performance cost. Finally, if your data is
still consistent after working for a while without DRI constraints in
place, there are probably other things keeping your data that way.
Examine the possibility of using triggers to enforce RI. Also consider
how users modify data. Perhaps DRI is unnecessary. Do you have a front
end that keeps things in agreement? Also keep in mind that DRI
constraints impose a performance penalty, although it is relatively
Ben McEwan, President, Geist, LLC