Very Computer

says...

Quote:> 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.)

You cannot use declarative referential integrity. That is, I cannot
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...

Quote:> It sounds like, ideally, an enterprise database should then be in one
> physical database.

Data that should have DRI constraints (data dependencies) between them
belong in the same database.

Quote:> 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.

You can write triggers to fix your current situation. You could use views
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
small.

--
Ben McEwan, President, Geist, LLC