473,320 Members | 2,027 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
---------------------------(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
3 5783
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
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

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Gunnar Vøyenli | last post by:
Hi! For the sake of simplicity, I have three tables, Employee, Department and Work Employee >---- Department \ / \ / ^ ^ Work
0
by: Grace | last post by:
Try our free demo www.nrgglobal.com DbVisualizer is a cross-platform database tool for all major relational databases. DbVisualizer enables simultaneous connections to many different databases...
6
by: Brendan Jurd | last post by:
Hi all, I read on the manual page for Inheritance that: "A limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single...
0
by: M. David Johnson | last post by:
I cannot get my OleDbDataAdapter to update my database table from my local dataset table. The Knowledge Base doesn't seem to help - see item 10 below. I have a Microsoft Access 2000 database...
3
by: Alex Satrapa | last post by:
There's some mention in the (old!) documentation that constraints such as foreign keys won't include data from inheriting tables, eg: CREATE TABLE foo ( id SERIAL PRIMARY KEY ); CREATE TABLE...
3
by: teedilo | last post by:
Our MS SQL (SQL Server 2000) DBA has database privileges locked down pretty tightly. We end users/developers do not have administrator privileges for most databases. That arrangement has worked...
7
by: Stephen Poley | last post by:
I have the following situation: - a table of employees, keyed on employee-id; - a table of training sessions, keyed on session-id; - a requirement to log who was present at which session, plus...
76
MMcCarthy
by: MMcCarthy | last post by:
Normalisation is the term used to describe how you break a file down into tables to create a database. There are 3 or 4 major steps involved known as 1NF (First Normal Form), 2NF (Second Normal...
2
by: astolpho | last post by:
I am using a slightly outdated reference book on J2EE programming. It gives 2 methods of creating a database used in its casestudies. The first is an ANT script that gives the following output: ...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.