Re: Unique constraint and NULL values

Hm... yes. I reduced the real-life problem a little too much. Actually I have a two-column unique constraint like this:

table t1(a integer not null, b integer)

I have the constraint on this two columns: a,b. Now what I want is this:

insert into t1 values(1, 1);    // ok
insert into t1 values(1, 2);     // ok
insert into t1 values(1, 1);    // error
insert into t1 values(1, null);    // ok
insert into t1 values(1, null);    // ok (in real-life: raises error)

Is it possible to enforce exactly this behavior?

"Tony Andrews" <andrewst_at_onetel.com> wrote in message news:1098363793.107241.90690_at_f14g2000cwb.googlegroups.com...
> Create a UNIQUE constraint and you are done:
>
> SQL> create table t1 (a integer null unique);
>
> Table created.
>
> SQL> desc t1
> Name Null? Type
> ------------------------------- -------- ----
> A NUMBER(38)
>
> SQL> insert into t1 values (1);
>
> 1 row created.
>
> SQL> insert into t1 values (1);
> insert into t1 values (1)
> *
> ERROR at line 1:
> ORA-00001: unique constraint (TANDREWS.SYS_C00164508) violated
>
>
> SQL> insert into t1 values (null);
> 1 row created.
>
> SQL> insert into t1 values (null);
>
> 1 row created.
>
Received on Thu Oct 21 2004 - 08:29:08 CDT