Re: composite key and further logical constraint

this should work

CREATE UNIQUE INDEX my_index ON my_table

    (DECODE(corptype,1,busid))

Janh

"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.
Received on Fri Sep 19 2003 - 01:56:54 CDT