Date: Sat, 22 Jul 2006 14:32:57 +0100
To: pgsql-sql@postgresql.org
Subject: Referential integrity (foreign keys) across multiple tables
Message-ID: <20060722133257.GA9785@furbychan.cocan.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.9i
From: Richard Jones <rich@annexia.org>


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;

Obviously that second statement isn't going to compile.

I don't want to add the hostid column to page_contents table because I
have a lot of old code accessing the database which would be hard to
change (the old code would no longer be able to insert page_contents
rows).

Is this possible somehow?  Perhaps by adding a second table?  Do I
have to use triggers, and if so is that as robust as referential
integrity?

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