Re: Unique constraint and NULL values

"Walt" <walt_askier_at_YourShoesyahoo.com> wrote in message news:41813FA3.7DD2584C_at_YourShoesyahoo.com...
| "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
| //
| //

hi, walt -- i got confused again looking at my own example

it's actually the 2nd and 3rd columns that were indexed in the example

so the question should be "since it's indeterminate whether (200,null) is a duplicate of (200,null) the
database should take it. ?"

maybe, but think of the consequences. i (dba/designer) have told the RDBMS "no way do i want any duplicates of the deptno, name combination!"

so, some user supplies the RDBMS with (200,null) and the RDBMS nervously compares this pair with all other pairs and decides, "well, no-one else has stored an unknown name with deptno 200, so i guess it's ok to accept this instance.

later, another user supplies the RDBMS with (200,null) and now the database gets really nervous: "oh! i've seen that before! someone else gave be deptno 200 but refused to tell me what name should be set to, and i took it! now, if i take this one, i really won't be sure if the name that the first user refused to tell me is really the same as the name that this user is refusing to tell me! and if the dba asks me whether they are the same or not , i'd have to say, 'i don't know!', and that would make the dba mad! so, i won't let this 2nd user give me a NULL until the 1st one fixes her NULL."

so, the RDBMS really can't accept the 2nd instance, because it would not be enforcing the UNIQUE constraint -- with one unknown value, it can confidently enforce the UNIQUE constraint ("i know there's only one department 200 unknown value, even though i don't know what it is"), but with 2, it would have to say "i dunno if they're unique!"

should the RDBMS have refused the first instance? no reason to, since the dba/designer didn't specify the NAME as a mandatory column

now, when you take this a step further and start looking at how composite FK references work with partial values, it really gets interesting....

++ mcs

(btw: i've been reading a lot of 'thomas the tank engine' recently, so it may have affected my posting style on this one) Received on Thu Oct 28 2004 - 14:24:33 CDT