Re: How do I constrain this?

From: Michael P. Stein <mstein_at_access2.digex.net>
Date: 14 Mar 1994 11:31:53 -0500
Message-ID: <2m23hp$naf_at_access2.digex.net>


Youichi Tamura <y-tamura_at_nriws11.nri.co.jp> wrote:
> by tomiii_at_mtu.edu
>> 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.
>
> Why not make person_id and primary_indicator the primary key
> for this table?
> This will ensure that the table has a unique row for every
> combination of person_id and primary_ind. It seems rather
> too simple to me.

    It is too simple. Suppose the person has three phone numbers? The primary has a value Y, the second one has a value N, and the third one will be rejected as a duplicate because you can't have a second 'N' for the same person_id.

    This could be made to work if the primary_indicator were changed to a phone_seq_no where '1' was considered the primary. However, I prefer the idea of putting the primary phone number in the person's master record.

-- 
Mike Stein			The above represents the Absolute Truth.
POB 10420			Therefore it cannot possibly be the official
Arlington, VA  22210		position of my employer.
Received on Mon Mar 14 1994 - 17:31:53 CET

Original text of this message