Walt wrote:
> "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.
Maybe I'm missing something but from the standpoint of your unique
constraint why should it see 200, NULL and 200,NULL as being different?
The primary key is irrelevant.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Thu Oct 28 2004 - 19:15:15 CDT
