Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C7952475A18; Mon, 19 Aug 2002 14:56:28 -0400 (EDT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 3433F4759D0; Mon, 19 Aug 2002 14:56:28 -0400 (EDT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 95A16D602; Mon, 19 Aug 2002 11:56:28 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 8B4DC5C04; Mon, 19 Aug 2002 11:56:28 -0700 (PDT) Date: Mon, 19 Aug 2002 11:56:28 -0700 (PDT) From: Stephan Szabo To: Darrin Domoney Cc: , , Subject: Re: [SQL] Urgent - SQL Unique constraint error (long) In-Reply-To: Message-ID: <20020819114639.S32955-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/216 X-Sequence-Number: 5331 On Mon, 19 Aug 2002, Darrin Domoney wrote: > Thanks for the response but the answer is no. Owing to the > ongoing issue with inherited tables in 7.x I have opted to create three > tables: > Contains generic traits regardless of "class or role". > Person -> PK person_id > > Staff as certain "class" of person. > Staff -> PK staff_id > FK person_id > > Contact as another "class" of person. > Contact -> PK contact_id > FK person_id > > Phone numbers relate to any "class" but are related back to > the originator by using "person_id". I now see what you're doing, but it won't work. ALTER TABLE phone_number ADD CONSTRAINT staff_phone FOREIGN KEY ( person_id ) REFERENCES staff ( person_id ) NOT DEFERRABLE; ALTER TABLE phone_number ADD CONSTRAINT contact_phone_number FOREIGN KEY ( person_id ) REFERENCES contact ( person_id ) NOT DEFERRABLE; means that the person_id in phone number must be in *both* contact and staff. Are there classes of person that you don't want phone_number to be able to reference? If not, you should be referencing person(person_id). If so, I'm not sure I have an answer for you apart from hacking triggers since even if inheritance worked, it wouldn't really help you there.