pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedForeign Key inter databases
4+ messages / 3 participants
[nested] [flat]
* Foreign Key inter databases
@ 2007-08-02 16:18 Dani Castaños <danitao.mailists@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Dani Castaños @ 2007-08-02 16:18 UTC (permalink / raw)
To: pgsql-sql
Hi all!
Is it possible to have a foreign key where referenced table is in
another database?
Thank you in advance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Foreign Key inter databases
@ 2007-08-02 19:23 Josh Tolley <eggyknap@gmail.com>
parent: Dani Castaños <danitao.mailists@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Josh Tolley @ 2007-08-02 19:23 UTC (permalink / raw)
To: Dani Castaños <danitao.mailists@gmail.com>; +Cc: pgsql-sql
On 8/2/07, Dani Castaños <danitao.mailists@gmail.com> wrote:
> Hi all!
>
> Is it possible to have a foreign key where referenced table is in
> another database?
>
> Thank you in advance
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
Unless you use things like dblink to make two separate databases think
they're one database, you really can't do anything where one database
depends on another. Your best bet, if you can have both data sets on
the same machine, is to stick them in two separate schemas, and then
you can have foreign keys on a table in one schema referencing a table
in another schema. That's (in part) what schemas are for in the first
place -- to separate logically distinct data sets while allowing
accesses between the two when necessary.
If for whatever reason having all the data on one machine isn't
possible, you can try using dbi-link or dblink to create links between
the two databases and do foreign keys that way, but I've never used
either, so that might not work/make sense/be possible, etc.
-Josh
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Foreign Key inter databases
@ 2007-08-03 07:24 Dani Castaños <danitao.mailists@gmail.com>
parent: Josh Tolley <eggyknap@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Dani Castaños @ 2007-08-03 07:24 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Josh Tolley escribió:
> On 8/2/07, Dani Castaños <danitao.mailists@gmail.com> wrote:
>
>> Hi all!
>>
>> Is it possible to have a foreign key where referenced table is in
>> another database?
>>
>> Thank you in advance
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo@postgresql.org so that your
>> message can get through to the mailing list cleanly
>>
>>
>
> Unless you use things like dblink to make two separate databases think
> they're one database, you really can't do anything where one database
> depends on another. Your best bet, if you can have both data sets on
> the same machine, is to stick them in two separate schemas, and then
> you can have foreign keys on a table in one schema referencing a table
> in another schema. That's (in part) what schemas are for in the first
> place -- to separate logically distinct data sets while allowing
> accesses between the two when necessary.
>
> If for whatever reason having all the data on one machine isn't
> possible, you can try using dbi-link or dblink to create links between
> the two databases and do foreign keys that way, but I've never used
> either, so that might not work/make sense/be possible, etc.
>
> -Josh
>
Thanks Josh!
I use dblinks for queries, but I'm pretty sure you can't use it in
constraints definitions.
My situation is that I have one main database and many customer's
databases. In main database there is a languages table, and customers
databases must use the language_id. Then, I don't want to create
languages table in each customer db, that's the reason why i was trying
to do a foreign key from customer db to main db.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Foreign Key inter databases
@ 2007-08-03 19:50 Erik Jones <erik@myemma.com>
parent: Dani Castaños <danitao.mailists@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Erik Jones @ 2007-08-03 19:50 UTC (permalink / raw)
To: Dani Castaños <danitao.mailists@gmail.com>; +Cc: pgsql-sql
On Aug 3, 2007, at 2:24 AM, Dani Castaños wrote:
> Josh Tolley escribió:
>> On 8/2/07, Dani Castaños <danitao.mailists@gmail.com> wrote:
>>
>>> Hi all!
>>>
>>> Is it possible to have a foreign key where referenced table is in
>>> another database?
>>>
>>> Thank you in advance
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 1: if posting/reading through Usenet, please send an appropriate
>>> subscribe-nomail command to majordomo@postgresql.org so
>>> that your
>>> message can get through to the mailing list cleanly
>>>
>>>
>>
>> Unless you use things like dblink to make two separate databases
>> think
>> they're one database, you really can't do anything where one database
>> depends on another. Your best bet, if you can have both data sets on
>> the same machine, is to stick them in two separate schemas, and then
>> you can have foreign keys on a table in one schema referencing a
>> table
>> in another schema. That's (in part) what schemas are for in the first
>> place -- to separate logically distinct data sets while allowing
>> accesses between the two when necessary.
>>
>> If for whatever reason having all the data on one machine isn't
>> possible, you can try using dbi-link or dblink to create links
>> between
>> the two databases and do foreign keys that way, but I've never used
>> either, so that might not work/make sense/be possible, etc.
>>
>> -Josh
>>
>
> Thanks Josh!
> I use dblinks for queries, but I'm pretty sure you can't use it in
> constraints definitions.
> My situation is that I have one main database and many customer's
> databases. In main database there is a languages table, and
> customers databases must use the language_id. Then, I don't want to
> create languages table in each customer db, that's the reason why i
> was trying to do a foreign key from customer db to main db.
Not directly as pg constraints, no. But, what you can do is create a
trigger that simulates the same effect.
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-08-03 19:50 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-02 16:18 Foreign Key inter databases Dani Castaños <danitao.mailists@gmail.com>
2007-08-02 19:23 ` Josh Tolley <eggyknap@gmail.com>
2007-08-03 07:24 ` Dani Castaños <danitao.mailists@gmail.com>
2007-08-03 19:50 ` Erik Jones <erik@myemma.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox