Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 8A2849FB391 for ; Fri, 3 Aug 2007 16:51:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 19154-04 for ; Fri, 3 Aug 2007 16:50:59 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from smtp115.iad.emailsrvr.com (smtp115.iad.emailsrvr.com [207.97.245.115]) by postgresql.org (Postfix) with ESMTP id C92739FA49D for ; Fri, 3 Aug 2007 16:51:07 -0300 (ADT) Received: by relay1.r1.iad.emailsrvr.com (Authenticated sender: erik-AT-myemma.com) with ESMTP id DE93C44C33D; Fri, 3 Aug 2007 15:51:06 -0400 (EDT) In-Reply-To: <46B2D823.7020700@gmail.com> References: <46B203BD.1000102@gmail.com> <46B2D823.7020700@gmail.com> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed Message-Id: Cc: pgsql-sql@postgresql.org Content-Transfer-Encoding: quoted-printable From: Erik Jones Subject: Re: Foreign Key inter databases Date: Fri, 3 Aug 2007 14:50:59 -0500 To: =?ISO-8859-1?Q?Dani_Casta=F1os?= X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/25 X-Sequence-Number: 28963 On Aug 3, 2007, at 2:24 AM, Dani Casta=F1os wrote: > Josh Tolley escribi=F3: >> On 8/2/07, Dani Casta=F1os wrote: >> >>> Hi all! >>> >>> Is it possible to have a foreign key where referenced table is in >>> another database? >>> >>> Thank you in advance >>> >>> ---------------------------(end of =20 >>> broadcast)--------------------------- >>> TIP 1: if posting/reading through Usenet, please send an appropriate >>> subscribe-nomail command to majordomo@postgresql.org so =20 >>> that your >>> message can get through to the mailing list cleanly >>> >>> >> >> Unless you use things like dblink to make two separate databases =20 >> 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 =20 >> 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 =20 >> 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 =20 > constraints definitions. > My situation is that I have one main database and many customer's =20 > databases. In main database there is a languages table, and =20 > customers databases must use the language_id. Then, I don't want to =20= > create languages table in each customer db, that's the reason why i =20= > 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 =20= trigger that simulates the same effect. Erik Jones Software Developer | Emma=AE 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