| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Unique Constraint question
see answer below
> -----Original Message-----
> From: Curiel, David [mailto:CURIELDA_at_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).
 |
 |