Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with SQL constraint
On Feb 16, 5:50 am, "William Robertson" <williamr2..._at_googlemail.com>
wrote:
> 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".
William - alas 'BYPASS_UJVC is no longer supported on 10g. I already tried that one successfully on 9.2i and it worked well. But thanks for the suggestion. Maybe if you know of an equivalent in 10g you could enlighten me?
Dean Received on Fri Feb 16 2007 - 14:41:55 CST