Re: NULLs

Walt wrote:
> Connor McDonald wrote:
> > Andrew Clark wrote:
>
> >>I have an application whose database was recently upgraded from 7 to 9i. In
> >>a bit of code, columns are compared that may both be null. In Oracle 7,
> >>will two columns compare equal if they are both NULL? In 9i it seems that
> >>they do not and I have to change it to NVL(column1) = NVL(column2).
> >
> > null = null never equated to true since I've been using oracle (6 and
> > above)
>
> One exception is a unique index on 2 or more columns. For instance:
>
> SQL> CREATE TABLE tmptbl
> 2 (
> 3 colA VARCHAR2(1),
> 4 colB VARCHAR2(1)
> 5 );
> Table created.
> SQL> CREATE UNIQUE INDEX tmptblidx
> 2 ON TMPTBL(COLA, COLB);
> Index created.
> SQL> insert into tmptbl (colA, colB) values ('1', '');
> 1 row created.
> SQL> insert into tmptbl (colA, colB) values ('1', '');
> insert into tmptbl (colA, colB) values ('1', '')
> *
> ERROR at line 1:
> ORA-00001: unique constraint (MTRACK.TMPTBLIDX) violated
>
> The null values in colB violate the unique index, so at some level these
> two nulls are considered "equal". This behavior surprised me the first
> time I encountered it.

Yes, it's appears contrary to what the SQL'92 standard says about multicolumn unique constraints (its language is quite cryptic though), but it has always been this way in Oracle.

>
> //Walt
Received on Wed Dec 21 2005 - 20:38:17 CST