composite key and further logical constraint

From: Canny <carolyn.derrick_at_gnb.ca>
Date: Thu, 18 Sep 2003 17:46:32 GMT
Message-ID: <Ybmab.12059$Ej.1746097@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 Thu Sep 18 2003 - 12:46:32 CDT