Re: NULLs

From: Walt <walt_askier_at_SHOESyahoo.com>
Date: Wed, 21 Dec 2005 16:54:02 -0500
Message-ID: <_9kqf.2386$yb2.592@news.itd.umich.edu>

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.

//Walt Received on Wed Dec 21 2005 - 15:54:02 CST