Thread: foreign key constraint across databases
Is it possible to have a column in Table-2 of Database-B to have a foreign key constraint referencing Table-1 of Database-A? If possible, how to implement it? Best regards, Siva Kumar -- Ma Siva Kumar, BSG LeatherLink, Chennai, India. http://www.leatherlink.net/
On Thu, 6 Mar 2003, Siva Kumar wrote: > Is it possible to have a column in Table-2 of Database-B to have a foreign key > constraint referencing Table-1 of Database-A? Not using the straight foreign key support (you might be able to put something together using dblink in your own triggers, but it'd probably be more trouble than it's worth)
On Thursday 06 March 2003 11:17 am, Stephan Szabo wrote: > On Thu, 6 Mar 2003, Siva Kumar wrote: > > Is it possible to have a column in Table-2 of Database-B to have a > > foreign key constraint referencing Table-1 of Database-A? > > Not using the straight foreign key support (you might be able to put > something together using dblink in your own triggers, but it'd probably be > more trouble than it's worth) That is disappointing. We really do need this feature for our application. I heard that Oracle, MS SQL Server etc support this. What can we do to implement this in Postgresql also? Where should we look for? and what skill set is needed to extend Postgresql capabilities? Sorry for asking such basic question in the list. We are a small company and would like to contribute, but require a little hand-holding in the beginning Best regards, Siva -- Ma Siva Kumar, BSG LeatherLink, Chennai, India.
On Thu, 6 Mar 2003, Siva Kumar wrote: > On Thursday 06 March 2003 11:17 am, Stephan Szabo wrote: > > On Thu, 6 Mar 2003, Siva Kumar wrote: > > > Is it possible to have a column in Table-2 of Database-B to have a > > > foreign key constraint referencing Table-1 of Database-A? > > > > Not using the straight foreign key support (you might be able to put > > something together using dblink in your own triggers, but it'd probably be > > more trouble than it's worth) > > That is disappointing. We really do need this feature for our application. I > heard that Oracle, MS SQL Server etc support this. What can we do to > implement this in Postgresql also? Where should we look for? and what skill > set is needed to extend Postgresql capabilities? > > Sorry for asking such basic question in the list. We are a small company and > would like to contribute, but require a little hand-holding in the beginning Might schemas provide the utility you're looking for?
On Thursday 06 March 2003 10:20 pm, you wrote: > On Thu, 6 Mar 2003, Siva Kumar wrote: > > On Thursday 06 March 2003 11:17 am, Stephan Szabo wrote: > > > On Thu, 6 Mar 2003, Siva Kumar wrote: > > > > Is it possible to have a column in Table-2 of Database-B to have a > > > > foreign key constraint referencing Table-1 of Database-A? > > > > > > Not using the straight foreign key support (you might be able to put > > > something together using dblink in your own triggers, but it'd probably > > > be more trouble than it's worth) > > > > That is disappointing. We really do need this feature for our > > application. I > > What are you planning to use it for (ie, why separate databases rather > than something like separate schemas) Our application is used by different companies for their operation and will be accessible over internet. We want to have separate databases for each company and the application will interact with all the databases to enable buyer-supplier relationships etc. With this we can offer local systems for large companies which can synchronize with the central server periodically. Basically we want to ensure that the data is owned by the concerned companies, and in case they want to break away from our service, we just take out their database and give to them. As far as I understand having separate schema will not server our purpose. We will have to look at other options now it seems. Best regards, Siva -- Ma Siva Kumar, BSG LeatherLink, http://www.leatherlink.net/
Siva Kumar <siva@leatherlink.net> writes:
> Is it possible to have a column in Table-2 of Database-B to have a
> foreign key constraint referencing Table-1 of Database-A?
>>
>> What are you planning to use it for (ie, why separate databases rather
>> than something like separate schemas)
> Our application is used by different companies for their operation and will be
> accessible over internet. We want to have separate databases for each company
> and the application will interact with all the databases to enable
> buyer-supplier relationships etc.
Let me get this straight: you want separable, removable databases for
each client --- but you are going to maintain foreign-key relationships
between them? Something needs rethinking here.
Either you need an airtight separation between clients, or you don't.
If you don't, then a schema per client ought to do the job. If you do,
I think foreign-key relationships are mighty questionable. Exactly
which client is going to have their queries fail when the FK constraint
is violated? And how are you going to explain the failure to the client
that thought they had an airtight private database?
regards, tom lane