Re: unique constraint - bug?

in oracle 8.1.6:
create table x(col1 char(1), col2 char(1), col3 char(1));

Table created.

SQL> desc x
Name Null? Type
------------------------------- -------- ----
COL1 CHAR(1)
COL2 CHAR(1)
COL3 CHAR(1)

create unique index xidx on x(col1,col2,col3);

Index created.

insert into x values ('1','2','3');

1 row created.

insert into x values ('1','','3');

1 row created.

insert into x values ('1','','3');

insert into x values ('1','','3')
*
ERROR at line 1:
ORA-00001: unique constraint (DBA_USER.XIDX) violated

i'd say that it finds 1,null,3 equal the 2nd time around...

mikeo

At 10:25 AM 7/20/00 -0400, Tom Lane wrote:
>"Oliver Elphick" <olly(at)lfix(dot)co(dot)uk> writes:
>> Tom Lane wrote:
>>> Two nulls are never considered equal, therefore the unique constraint
>>> does not trigger.
>
>> I think you are not interpreting this right:
>
>> A unique constraint is satisfied if and only if no two rows in
>> a table have the same non-null values in the unique columns.
>
>> I think it means that nulls are to be left out of account in the
>> comparison.
>
>Hmm. What then of
>
> a, b, c
> a, NULL, c
>
>If I "ignore the null" then these two rows are equal as well.
>
>Still, you're right that it's a little more ambiguously worded than
>I thought. Can anyone check how other DBMSs handle this?
>
> regards, tom lane
>