Re: How do I constrain this?
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