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
>
