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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with SQL constraint

Re: Help with SQL constraint

From: William Robertson <williamr2019_at_googlemail.com>
Date: 16 Feb 2007 02:50:44 -0800
Message-ID: <1171623044.477151.40200@m58g2000cwm.googlegroups.com>


On Feb 15, 2:37 am, "dean" <deanbrow..._at_yahoo.com> wrote:
> Hello all,
>
> A table T has 2 fields, one (L) holding letters 'Y' and 'N', and one
> (X) holding numbers. Is there a (non trigger) constraint such that for
> L='N' (and only this letter) the numbers must be unique? Records
> where L='Y' do not have to be unique.
>
> (I need to join another table to the L='N' group of records, and the
> join must be key-preserved).
>
> Cheers,
>
> Dean

The standard "conditional uniqieness" approach is to use a unique function based index using a DECODE or similar expression. Whether this will count as a key preservation device I don't know. If not the alternative would be to use the (undocumented, known to stop working on patch releases) hint 'BYPASS_UJVC". Received on Fri Feb 16 2007 - 04:50:44 CST

Original text of this message

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