Обсуждение: Cross database foreign key workaround?

List,
    What are the recommended work arounds for cross database foreign keys?
As I understand it transactions are not atomic with the TCL method.  I have
a situation that requires a master database and then a separate database for
every subscriber.  Subscribers need read/write to both databases.  I chose
separate databases because there are 20+ large tables that would require
uid/gid columns, indexes and where conditions to separate information by
subscriber.  I thought that was too much overhead.  Should I just use my
application to make changes and ensure references that need to take place
across databases?  Or should I add a uid/gid to all necessary tables, create
indexes and update all necessary where clauses?  Ideas?

/B

David Busby wrote:
> List,
>     What are the recommended work arounds for cross database foreign keys?
> As I understand it transactions are not atomic with the TCL method.  I have
> a situation that requires a master database and then a separate database for
> every subscriber.  Subscribers need read/write to both databases.  I chose
> separate databases because there are 20+ large tables that would require
> uid/gid columns, indexes and where conditions to separate information by
> subscriber.  I thought that was too much overhead.  Should I just use my
> application to make changes and ensure references that need to take place
> across databases?  Or should I add a uid/gid to all necessary tables, create
> indexes and update all necessary where clauses?  Ideas?
>

What about using schemas?

Joe

El Mié 08 Oct 2003 18:46, David Busby escribió:
> List,
>     What are the recommended work arounds for cross database foreign keys?
> As I understand it transactions are not atomic with the TCL method.  I have
> a situation that requires a master database and then a separate database
> for every subscriber.  Subscribers need read/write to both databases.  I
> chose separate databases because there are 20+ large tables that would
> require uid/gid columns, indexes and where conditions to separate
> information by subscriber.  I thought that was too much overhead.  Should I
> just use my application to make changes and ensure references that need to
> take place across databases?  Or should I add a uid/gid to all necessary
> tables, create indexes and update all necessary where clauses?  Ideas?

Use schemas. That's what they are for! (at least thats the main reason we are
using them intensivelly).

--
 19:28:01 up 6 days,  5:05,  2 users,  load average: 0.36, 0.40, 0.36
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

You can try to use dblink (function returning results
from a remote database)and create some triggers with
it in order to make remote referential integrity.

Or if there's a lot of links between the tables in the
2 databases it may be better to use one database.




--- David Busby <busby@pnts.com> wrote:
> List,
>     What are the recommended work arounds for cross
> database foreign keys?
> As I understand it transactions are not atomic with
> the TCL method.  I have
> a situation that requires a master database and then
> a separate database for
> every subscriber.  Subscribers need read/write to
> both databases.  I chose
> separate databases because there are 20+ large
> tables that would require
> uid/gid columns, indexes and where conditions to
> separate information by
> subscriber.  I thought that was too much overhead.
> Should I just use my
> application to make changes and ensure references
> that need to take place
> across databases?  Or should I add a uid/gid to all
> necessary tables, create
> indexes and update all necessary where clauses?
> Ideas?
>
> /B
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose
> an index scan if your
>       joining column's datatypes do not match


__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com