Re: Foreign Key inter databases

On Aug 3, 2007, at 2:24 AM, Dani Castaños wrote:

> Josh Tolley escribió:
>> On 8/2/07, Dani Castaños <danitao(dot)mailists(at)gmail(dot)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(at)postgresql(dot)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(at)myemma(dot)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