Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Unique Constraint question

RE: Unique Constraint question

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 04 May 2001 13:51:48 -0700
Message-ID: <F001.002F9DB1.20010504132044@fatcity.com>

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).



Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com Received on Fri May 04 2001 - 15:51:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US