From: Erik Jones <erik@myemma.com>
To: Dani Castaños <danitao.mailists@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Foreign Key inter databases
Date: Fri, 3 Aug 2007 14:50:59 -0500
Message-ID: <AEF29B30-FC25-4DD6-A7B4-CFC544F31760@myemma.com> (raw)
In-Reply-To: <46B2D823.7020700@gmail.com>
References: <46B203BD.1000102@gmail.com>
<e7e0a2570708021223n1e6e234dtbd6f04f6e9089189@mail.gmail.com>
<46B2D823.7020700@gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: erik@myemma.com, danitao.mailists@gmail.com
Subject: Re: Foreign Key inter databases
In-Reply-To: <AEF29B30-FC25-4DD6-A7B4-CFC544F31760@myemma.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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