Re: How do I constrain this?

In article <1994Mar19.060849.23283_at_oracle.us.oracle.com: Michael Friedman, mfriedma_at_us.oracle.com 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.

Actually, it seems to me that this would be pretty easy. Just add a trigger to INSERT and UPDATE that, if the primary_indicator is true, selects to see if there is already a (different) primary phone_tab entry that has the same person and raises an exception if it finds one. Might be simpler than maintaining two identically structured tables. Received on Wed Mar 23 1994 - 03:30:39 CET