Re: How do I constrain this?

In article <2l5orl$9ei_at_bambam.cts.mtu.edu>, tomiii_at_mtu.edu (Thomas Dwyer III) wrote:

> 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.
>
>
> Thanks,
> Tom.III
> tomiii_at_mtu.edu

I would look at this problem differently.

If a person can have only one primary phone number, then the primary phone number is an attribute of the *person* and belongs in the person table.

In addition, the primary phone number must be one of the person's phone numbers. Hence, you would need a foreign key constraint from the person table to the phone table requring the combination of (person,primary_phone) to be in the phone table. In the phone table, the combination of person and phone should probably be the primary key.

If you have the key references set up this way, I don't think any triggers are needed to keep it straight.

The user-interface is another issue. I can imagine a zillion different ways to do it, but I don't see a very strong reason not to show the primary phone # in a separate field, with all the #'s in a scrolling list. (Thus, the primary would appear twice.)

Questions you need to answer:

1. How does the user change the primary # to one not in the list?
2. How does the user change the primary # to a different member of the list?
3. How does the user change a non-primary #?

I think a very reasonable answer to all three is that the user simply types in the new number.

If the user wants to change the primary # to one not on the list, they might type over the number on the list that matches the primary #. In response to this, you need to first set the primary to be null, then change the list, then update the primary.

The user might also type over the primary number with a number not on the list. You *could* reject this, or you could first add the new number to the list, then change the primary, and delete the old primary from the list.

Changing the primary to another # on the list or changing a non-primary should not be a problem. Received on Thu Mar 10 1994 - 19:11:36 CET