Re: How do I constrain this?

In article q6o_at_anaxagoras.ils.nwu.edu, Kevin Neel <k-neel_at_nwu.edu> () writes:
>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.

A trigger would be nice -- but -- In oracle 7 a trigger that is fired by a table modification cannot query information in the table that is currently being modified -- the table is in a state of flux and you will get a MUTATING TABLE error message (in my experience).

---

--------------------------------
Steven C. Nelson
Amoco Production Company
Exploration Systems & Services 
scnelson_at_amoco.com
--------------------------------