Re: [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.