Re: Referential integrity (foreign keys) across multiple tables

On Mon, Jul 24, 2006 at 12:26:15PM -0500, Aaron Bono wrote:
> On 7/22/06, Richard Jones <rich(at)annexia(dot)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