Re: composite key and further logical constraint

"Canny" <carolyn.derrick_at_gnb.ca> a écrit dans le message de 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.
>

You can use triggers on insert and update. You'll find some useful informations on Tom Kyte site at url: http://osi.oracle.com/~tkyte/Mutate/index.html

Hope this helps
Michel Cadot Received on Thu Sep 18 2003 - 16:11:29 CDT