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: dean <deanbrown3d_at_yahoo.com>
Date: 27 Feb 2007 08:09:57 -0800
Message-ID: <1172592597.196915.249680@8g2000cwh.googlegroups.com>


On Feb 14, 9:37 pm, "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

I wanted to add the solution here for others, as I think its elegant.
>From Tom Kyte's book Effective Oracle by Design:

create table T (L varchar2(1) not null, X number not null); $ Table created.

$ Index created.

insert into T (L, X) values ('Y', 1);

$ 1 row created.

insert into T (L, X) values ('Y', 2);

$ 1 row created.

insert into T (L, X) values ('Y', 1);

$ 1 row created.

insert into T (L, X) values ('N', 1);

$ 1 row created.

insert into T (L, X) values ('N', 2);

$ 1 row created.

insert into T (L, X) values ('N', 1);

$ insert into T (L, X) values ('N', 1)
$ *
$ ERROR at line 1:
$ ORA-00001: unique constraint (MY_IDX) violated

Dean Received on Tue Feb 27 2007 - 10:09:57 CST

Original text of this message

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