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