From Jacques.Kilchoer@quest.com Fri, 04 May 2001 13:51:48 -0700 From: Jacques Kilchoer Date: Fri, 04 May 2001 13:51:48 -0700 Subject: RE: Unique Constraint question Message-ID: MIME-Version: 1.0 Content-Type: text/plain Title: RE: Unique Constraint question see answer below > -----Original Message----- > From: Curiel, David [mailto:CURIELDA@phibred.com] > > I've scoured the docs and found nothing. My situation is this: > > How do I set up a constraint on a table, where I want a > unique combination > of fields, only when one of them equals a certain value? > > For instance: > > Col1     Col2 > ----       ------ > N          1       <= OK > N          0       <= OK > N          0       <= OK > N          1       <= NOT OK > > It obviously cannot be done through basic column constraints, > since it is OK > to have multiple N,0 combinations. > > My sense is that there would have to be a trigger to affect a > query lookup > of the N,1 combination prior to insert.  However, I > desparately want to > avoid that for performance reasons. What version of Oracle? My first thought would be to have a "dummy" column that's part of the unique constraint. The "dummy" column would be set to null via a "before insert" trigger on the table when col2 != 0, and would be set to the next number of a sequence when col2 = 0. This would allow duplicates for (n, 0) but not for (n, 1). ------ Jacques R. Kilchoer (949) 754-8816 Quest Software, Inc. 8001 Irvine Center Drive Irvine, California 92618 U.S.A. http://www.quest.com