Re: composite key and further logical constraint

"Canny" <carolyn.derrick_at_gnb.ca> wrote in message news:<Ybmab.12059$Ej.1746097_at_ursa-nb00s0.nbnet.nb.ca>...
> Using Oracle version 9.2
>
> create table mytable
> (busid number(4) ,
> corpid varchar2(30),
> corptype number(4),
> CONSTRAINT mytable_pk PRIMARY KEY (busid, corpid)
> )
>
> How can we ensure that for each busid there is only one corpid with
> corptype=1? It is ok for a busid to have many cases of corpid of corptype 2
> or corptype 3.
>
> I believe I once saw a simple solution (trick) for this on Ask Tom that
> would work for Oracle 8i and above. I tried to find it again but was not
> able to compose a search string that would bring it back.
>
> Thanks.

Canny, your requirements violate the rules of normalization so the proper solution is to rework the design using multiple tables. The simple solution is to query the table before you insert the row and have the application reject rows that would be in violation of the requirement.

However, for rules like yours developes usually try to create table triggers then they come see me and ask why they are getting a mutating table trigger. Oracle has published a white paper on getting around mutating table errors by using a combination of a for each row and a statement trigger. I suspect that this is what you saw at AskTom. The one time I tried following the instructions the mutating table error still came up, but then the developers requirements were a little more complicated since he had to validate data against another table and change existing row data due to the insert in some cases.

Someone else may have a better idea.

HTH -- Mark D Powell -- Received on Thu Sep 18 2003 - 20:50:09 CDT