Re: How do I constrain this?

I didn't see the original but...

In article <2lhune$23b_at_usenet.INS.CWRU.Edu> bs794_at_cleveland.Freenet.Edu (Diana Tracy) writes:

>In a previous article, tomiii_at_mtu.edu (Thomas Dwyer III) says:
 

>>I have the following table in an Oracle v7 database:
 

>> create table phone_tab (
>> person_id char(9) not null,
>> phone char(10) not null,
>> primary_indicator char(1)
>> constraint check_primary_indicator
>> check (primary_indicator in ('Y', 'N'))
>> );
 

>>Each person may have an unlimited number of phone numbers but only
>>one can be the primary phone number at any given time. How can I
>>create a constraint/trigger/whatever which enforces the statement:
 

>> For every person_id there must be exactly one row where primary_ind='Y'
 

>>Is this even possible? Any suggestions would be appreciated.

I'm sure you can do this, but it is likely to be painful and to cause more problems in the future. Perhaps you need to step back and think about this.

It seems to me that you have two entities - Person and Phone Number. The Phone Number entity has two sub-entities - Primary Phone Number and Non-Primary Phone Number.

Your logic then becomes

Every Person must be assigned one and only one Primary Phone Numbers.

Every Primary Phone Number may belong to one and only one Person.

(Note that this means that you must create a Primary Phone Number before you can create its owner. Is this really what you want? Do you always know a Person's Primary Phone Number?)

Every Person may be assigned one or more Non-Primary Phone Numbers

Every Non-Primary Phone Number may belong to one and only one Person.

At this point, your system is quite easy to build with foreign key constraints. Received on Sat Mar 19 1994 - 07:08:49 CET