Unique Constraint question

From: Curiel, David <CURIELDA_at_phibred.com>
Date: Fri, 04 May 2001 12:07:54 -0700
Message-ID: <F001.002F9832.20010504072149@fatcity.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.

Any help is appreciated.
Thanks

David Curiel
Bioinformatics Software Engineer
Pioneer Hi-Bred Int'l
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Curiel, David
INET: CURIELDA_at_phibred.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri May 04 2001 - 14:07:54 CDT