From: Richard Jones <rich@annexia.org>
To: Aaron Bono <postgresql@aranya.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Referential integrity (foreign keys) across multiple tables
Date: Mon, 24 Jul 2006 18:57:51 +0100
Message-ID: <20060724175750.GA19900@furbychan.cocan.org> (raw)
In-Reply-To: <bf05e51c0607241026y5404fb9do86a2af7f1cde74ac@mail.gmail.com>
References: <20060722133257.GA9785@furbychan.cocan.org>
<bf05e51c0607241026y5404fb9do86a2af7f1cde74ac@mail.gmail.com>
On Mon, Jul 24, 2006 at 12:26:15PM -0500, Aaron Bono wrote:
> On 7/22/06, Richard Jones <rich@annexia.org> 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
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: rich@annexia.org, postgresql@aranya.com
Subject: Re: Referential integrity (foreign keys) across multiple tables
In-Reply-To: <20060724175750.GA19900@furbychan.cocan.org>
* 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