Re: NULLs

vc wrote:
> > 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.

Can you tell us what part of the SQL '92 standard it says this? I took a look at the standard, or rather the interpretation of it, by C.J. Date, "A Guide to The SQL Standard", 3rd ed. reprinted with corrections December 1994, Addison Wesley. On p.235 in the section "Duplicate Elimination", it says

... Left and Right are defined to be duplicates of one another if and only if, for all i in the range 1 to n, either "Li = Ri" is true, or Li and Ri are both null.

Here his "Left" and "Right" correspond to two rows (feel free to correct my understanding) and Li and Ri refer to each column of the two rows. The above statement in Yong's language says these two rows are duplicates

"a" 123 "some string"
"a" 123 "some string"

So are these two

null null null
null null null

But not these two

"a" 123 null
"a" 123 null

That is, you can't mix a null in any column. Oracle seems to follow the standard quite well:

SQL> create table allnulltest (a number, b number);

Table created.

SQL> create unique index unq_allnulltest on allnulltest (a, b);

Index created.

SQL> insert into allnulltest values (null, null);

1 row created.

SQL> / 1 row created.

SQL> insert into allnulltest values (1, null);

1 row created.

SQL> /
insert into allnulltest values (1, null) *
ERROR at line 1:
ORA-00001: unique constraint (YHUANG.UNQ_ALLNULLTEST) violated

Yong Huang Received on Wed Dec 21 2005 - 23:06:49 CST