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@lfix.co.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
>