see answer below
> -----Original Message-----
> From: Curiel, David [mailto:[EMAIL PROTECTED]]
>
> 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
