 | |
Re: Unique constraint and NULL values
comp.databases.oracle comp.databases.oracle
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: Unique constraint and NULL values -
10-28-2004
, 01:51 PM
|
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
//
//
#2
| |||
| |||
|
|
"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 // // |
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)
#3
| |||
| |||
|
|
David Portas wrote: There is no documented method of making unique constraints ANSI-compliant in MS SQL Server 7.0 or 2000. There are some workarounds but no supported configuration parameter for changing the unique constraint behaviour. I would be very surprised if there is such an undocumented feature but if you know differently can you post an example or a reference? I'm not a SQL Server expert but Tom Kyte makes reference to it in Expert One-on-one Oracle. I've loaned my copy of the book to another instructor until he gets his own so I can't quote the page and paragraph. If Kyte says it is there ... I'm inclined toward believing that it is, indeed, there. |
in my copy of Tom Kytes "Expert 101 Oracle" he makes no such
reference; actually he implies (page 38) that setting a specific
variable to null
("L-SOME_VARIABLE" he calls it) in a piece of coding a certain query
didn't
return rows while a SQL Server schooled developer expected it to.
He doesn't suggest that this is an undocumented feature or that such
a variable really exists, but that this is solely due to a difference
between Oracle and SQL Server (or Sybase) in the handling of null
values
Hans Wijte
#4
| |||
| |||
|
|
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? |
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.
Quote:
|
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? |
as I want into one of the columns, as long as each is paired with a
different value for the other column.
Quote:
|
Paraphrasing Frank Piron (above) , it seems that Oracle is treating null as an identifying value, which doesn't make sense to me. |
[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
#5
| |||
| |||
|
|
Walt <walt_askier (AT) YourShoesyahoo (DOT) com> wrote: 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. |
That's my hunch as well - it was easier to implement that way, and once
it was released with that behavior they can't change it at this point.
Maybe I'm being too much of a purist, but it seems the actual behavior
is less than ideal.
Quote:
|
[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 |
low level. As you suggest, the behavior is the same. And if it walks
like a duck...
--
//-Walt
//
//
#6
| |||
| |||
|
|
I want to enforce such a constraint on a column that would ensure that the values be all unique, but this wouldn't apply to NULL values. (I.e. there may be more than one NULL value in the column.) How can I achieve this? I suppose I would get the most-hated "table/view is changing, trigger/function may not see it" error if I tried to write a trigger that checks the uniqueness of non-null values upon insert/update. |
#7
| |||
| |||
|
|
Not sure anyone really answered this before they went off on the tangent. I believe you can create an after-insert statement-level trigger (not for each row) that CAN read the after-insert state of the subject table without mutating. So in that trigger, do something like: var := 0 ; select 1 into var from dual where exists ( select count(value), value from table where value is not null group by count(value) having count(value) > 1 ) ; -- then test var, if it is a 1, then raiserror cuz some non-null value was in there twice or more. "Agoston Bejo" <gusz1 (AT) freemail (DOT) hu> wrote in message news:cl8ba7$d04$1 (AT) news (DOT) caesar.elte.hu... I want to enforce such a constraint on a column that would ensure that the values be all unique, but this wouldn't apply to NULL values. (I.e. there may be more than one NULL value in the column.) How can I achieve this? I suppose I would get the most-hated "table/view is changing, trigger/function may not see it" error if I tried to write a trigger that checks the uniqueness of non-null values upon insert/update. |
advantage of reading from GOOGLE).
The answer (to be repetitious) is to use the UNIQUE CONSTRAINT on the
column. It has precisely the behavior desired. No triggers need apply
for this job.
HTH,
ed
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.