By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,773 Members | 1,724 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,773 IT Pros & Developers. It's quick & easy.

Cross database foreign key workaround?

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #2

P: n/a
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 | mm******@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #3

P: n/a

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 <bu***@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.