RE: Unique Constraint question

From: Hillman, Alex <Alex.Hillman_at_usmint.treas.gov>
Date: Fri, 04 May 2001 13:23:50 -0700
Message-ID: <F001.002F9D2C.20010504123531@fatcity.com>

You should use table level check constraint. See docs.

Alex Hiilman

-----Original Message-----
Sent: Friday, May 04, 2001 11:22 AM
To: Multiple recipients of list ORACLE-L

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

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

Author: Hillman, Alex
  INET: Alex.Hillman_at_usmint.treas.gov

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 - 15:23:50 CDT