Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with SQL constraint
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