X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 0E6379FA6B1 for ; Mon, 24 Jul 2006 14:58:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 54003-04 for ; Mon, 24 Jul 2006 14:58:00 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from furbychan.cocan.org (furbychan.cocan.org [80.68.91.176]) by postgresql.org (Postfix) with ESMTP id 521D69FA4C5 for ; Mon, 24 Jul 2006 14:57:59 -0300 (ADT) Received: from rich by furbychan.cocan.org with local (Exim 3.35 #1 (Debian)) id 1G54gh-0006wp-00; Mon, 24 Jul 2006 18:57:51 +0100 Date: Mon, 24 Jul 2006 18:57:51 +0100 To: Aaron Bono Cc: pgsql-sql@postgresql.org Subject: Re: Referential integrity (foreign keys) across multiple tables Message-ID: <20060724175750.GA19900@furbychan.cocan.org> References: <20060722133257.GA9785@furbychan.cocan.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.9i From: Richard Jones X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.075 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200607/301 X-Sequence-Number: 25875 On Mon, Jul 24, 2006 at 12:26:15PM -0500, Aaron Bono wrote: > On 7/22/06, Richard Jones wrote: > > create table hosts ( > > id serial primary key, > > hostname text not null > > ); > > > > create table pages ( > > id serial primary key, > > hostid int not null references hosts (id), > > url text not null, > > unique (hostid, url) > > ); > > > > create table page_contents ( > > pageid int not null references pages (id), > > section text not null > > ); > > > >(There are many hosts, many pages per host, and many page_contents > >sections per page). > > > >Now I want to add a column to page_contents, say called link_name, > >which is going to reference the pages.url column for the particular > >host that this page belongs to. > > > >Something like: > > > > alter table page_contents add link_name text; > > alter table page_contents > > add constraint foo foreign key (p.hostid, link_name) > > references pages (hostid, url) > > where p.id = pageid; > > The easiest, and not necessarily elegant, way to acomplish this is to create > linkid rather than link_name and make it a foreign key to pages.id. Unfortunately this isn't possible :-( My schema above is simplified a little too far. In the real schema we keep old versions of pages around in the pages table (we move the 'url' field to a 'url_deleted' field so that the unique (hostid, url) isn't violated by the new version of the page). This means that the pageid can be updated, so link_name must store a url, not a pageid. > Then add a trigger that checks to make sure the pages you link to > from page_contents to pages is for the same host. If not, raise an > exception. I think though that this suggestion is right. I'm not sure what difference it makes if it's link_name or linkid, but it looks like I'll have to write a trigger for this. It doesn't seem like there's a way using just ordinary foreign keys. Rich. -- Richard Jones, CTO Merjis Ltd. Merjis - web marketing and technology - http://merjis.com Team Notepad - intranets and extranets for business - http://team-notepad.com