From: Richard Jones <rich@annexia.org>
To: pgsql-sql@postgresql.org
Subject: Referential integrity (foreign keys) across multiple tables
Date: Sat, 22 Jul 2006 14:32:57 +0100
Message-ID: <20060722133257.GA9785@furbychan.cocan.org> (raw)
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
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
Subject: Re: Referential integrity (foreign keys) across multiple tables
In-Reply-To: <20060722133257.GA9785@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