From: "Darrin Domoney" <ddomoney@emergingfrontiers.ca>
To: <pgsql-sql@postgresql.org>, <pgsql-novice@postgresql.org>
Subject: Re: [ADMIN] [SQL] Urgent - SQL Unique constraint error (long)
Date: Mon, 19 Aug 2002 14:29:03 -0500
Message-ID: <FEEFJMJKKPINIKCCCCBJIEFHCAAA.ddomoney@emergingfrontiers.ca>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <20020819114639.S32955-100000@megazone23.bigpanda.com>
Importance: Normal


Stephen,
	I preemptivelty sensed your reply ;-) I took another look at the ERD
and quickly revised phone, address, and email so they all reference the
person
without having to become intertwined with their extended "class". Ultimately
this should prove more flexible as it will allow persons to shift from class
to class if their roles change.

Darrin

Inheritance would have made things so much easier .....

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Stephan Szabo
Sent: August 19, 2002 1:56 PM
To: Darrin Domoney
Cc: pgsql-sql@postgresql.org; pgsql-novice@postgresql.org;
pgsql-admin@postgresql.org
Subject: Re: [ADMIN] [SQL] Urgent - SQL Unique constraint error (long)


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.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly