Re: NULLs

yong321_at_yahoo.com wrote:
> 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?

" 8.9 <unique predicate>

     [...]

         2) If there are no two rows in T such that the value of each column

            in one row is non-null and is equal to the value of the cor-

            responding column in the other row according to Subclause 8.2,

            "<comparison predicate>", then the result of the <unique predi-

            cate> is true; otherwise, the result of the <unique predicate>

            is false.
"

>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:

That is not correct, but probably unimportant at this point.

[...]
>
> Yong Huang
Received on Thu Dec 22 2005 - 05:17:54 CST