Re: Unique constraint and NULL values

"Mark C. Stock" wrote:

> i'm sure you were responding specifically to the issue multi-column unique
> constraints (indexes) but just to make it clear for any neophytes listening
> in:
>
> this is legal (single-column unique constraint, multiple rows with NULL
> value):
> -----------------------------------------------------------------
> SQL> create table uk_demo (
> 2 id number constraint uk_demo$pk primary key
> 3 , name varchar2(30) constraint uk_demp$uk unique
> 4 );
> Table created.
>
> SQL> insert into uk_demo values (1,null);
> 1 row created.

> SQL> insert into uk_demo values (2,null);
> 1 row created.

> 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? 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? Paraphrasing Frank Piron (above) , it seems that Oracle is treating null as an identifying value, which doesn't make sense to me.

-- 
//-Walt
// 
//