X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 0D50F9FA502 for ; Mon, 24 Jul 2006 14:26:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 20726-03 for ; Mon, 24 Jul 2006 17:26:21 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.189]) by postgresql.org (Postfix) with ESMTP id D39869FA4C5 for ; Mon, 24 Jul 2006 14:26:19 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id p77so1561862nfc for ; Mon, 24 Jul 2006 10:26:15 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=fJwcWmUiDugVdeUR0xVDQZIiQ8TrYD5xrSlhMBYRFsUu69tVhdhV+atXv+9A7BzHqS6fBvmEEBEpGT3EgXAZjoBQqpPi7BUIg2JLkU03Qs0cQ3HFXrHO2/mYPVoGHb29spr0IvQoCwC//6+j3lKB4L5lc0JVex7SREyKM5TlCMQ= Received: by 10.78.139.5 with SMTP id m5mr1696588hud; Mon, 24 Jul 2006 10:26:15 -0700 (PDT) Received: by 10.78.32.10 with HTTP; Mon, 24 Jul 2006 10:26:15 -0700 (PDT) Message-ID: Date: Mon, 24 Jul 2006 12:26:15 -0500 From: "Aaron Bono" To: "Richard Jones" Subject: Re: Referential integrity (foreign keys) across multiple tables Cc: pgsql-sql@postgresql.org In-Reply-To: <20060722133257.GA9785@furbychan.cocan.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_129272_32795487.1153761975444" References: <20060722133257.GA9785@furbychan.cocan.org> X-Google-Sender-Auth: 81887abc8e281e6d X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.374 tagged_above=0 required=5 tests=HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200607/298 X-Sequence-Number: 25872 ------=_Part_129272_32795487.1153761975444 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/22/06, Richard Jones wrote: > > > Simplified schema: > > 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. 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. Another option is to do this: create table page_contents ( hostid int not null, url text not null, linkhostid int, linkurl text, section text not null, foreign key (hostid, url) references pages (hostid, url), foreign key (linkhostid, linkurl) references pages (hostid, url) ); Or if you really want to restructure things: create table hosts ( id serial primary key, hostname text not null ); create table pages ( id serial primary key, url text not null, unique (url) ); create table page_contents ( pageid int not null references pages (id), hostsid int not null references hosts (id), linkpageid int references pages(id), section text not null ); That should give you some options to play with. As a side comment, you should also name your primary key columns more meaningfully. Use hosts.hostsid and pages.pagesid, not hosts.id and pages.id. When you begin writing large queries, the use of the column name id all over the place will make your queries more prone to error, harder to read and harder to write. ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ================================================================== ------=_Part_129272_32795487.1153761975444 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/22/06, Richard Jones <rich@annexia.org> wrote:

Simplified schema:

  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.  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.

Another option is to do this:

 create table page_contents (
   hostid int not null,
   url text not null,
   linkhostid int,
   linkurl text,
   section text not null,
   foreign key (hostid, url) references pages (hostid, url),
   foreign key (linkhostid, linkurl) references pages (hostid, url)
 );

Or if you really want to restructure things:

 create table hosts (
   id serial primary key,
   hostname text not null
 );

 create table pages (
   id serial primary key,
   url text not null,
   unique (url)
 );

 create table page_contents (
   pageid int not null references pages (id),
   hostsid int not null references hosts (id),
   linkpageid int references pages(id),
   section text not null
 );

That should give you some options to play with.

As a side comment, you should also name your primary key columns more meaningfully.  Use hosts.hostsid and pages.pagesid, not hosts.id and pages.id.  When you begin writing large queries, the use of the column name id all over the place will make your queries more prone to error, harder to read and harder to write.

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
================================================================== ------=_Part_129272_32795487.1153761975444--