Thread: 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