Re: Unique constraint and NULL values

Walt <walt_askier_at_YourShoesyahoo.com> wrote:
>
> > this is not (multi-column unique constraint, dups in non-null
> > column(s)):
> > ----------------------------------------------------------------- SQL>
> > create table uk_demo2(
> > 2 id number constraint uk_demo2$pk primary key
> > 3 , deptno number
> > 4 , name varchar2(30)
> > 5 , constraint uk_demo2$uk unique ( deptno, name )
> > 6 );
> > Table created.
> >
> > SQL> insert into uk_demo2 values(1,200,null);
> > 1 row created.
> >
> > SQL> insert into uk_demo2 values(2,200,null);
> > insert into uk_demo2 values(2,200,null)
> > *
> > ERROR at line 1:
> > ORA-00001: unique constraint (SCOTT.UK_DEMO2$UK) violated
>
> Sorry for being a bit late to the party here, but this is a question
> that's bothered me for about 5 years, ever since I first "discovered"
> it.
>
> Anybody have a good rationale *why* it works this way?

I have a rationale. I don't think it is any grand philosophical decision, but rather just laziness. If all the columns of a concatenated index are null, then the overall key is null. Since Oracle doesn't index nulls (I don't know why), then there is no way to enforce uniqueness of the null key even if you wanted to. OTOH, if any column of the composite is not null, then the overall key is not null (concatenation[1] seems to be an exception to the rule that ordinary operations on nulls return null), and it is indexed, and on a unique index the default would be to disallow duplication. Rather than going through the extra work of having unique composite indices do a special case check for any of the columns being null, they just left it that way.

Well, that's my speculation, anyway.

> It seems to me
> that this should not be a violation of the uniqueness i.e. since it's
> indeterminate whether (1,200,null) is a duplicate of (2,200,null) the
> database should take it. I know the database won't, but I don't
> understand the reasoning.
>
> Why the one-null-per-column requirement?

There isn't a one-null-per-column allowance. I can put as many nulls as I want into one of the columns, as long as each is paired with a different value for the other column.

> Paraphrasing Frank Piron
> (above) , it seems that Oracle is treating null as an identifying value,
> which doesn't make sense to me.

Xho

[1] Yes, I know that the concatenation used in composite indices is not identical to ordinary string concatenation, but in this case it seems to operate pretty much the same way

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB