On Mon, Jul 24, 2006 at 12:26:15PM -0500, Aaron Bono wrote: > On 7/22/06, Richard Jones <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
