Unique constraint and NULL values

**Mark C. Stock** · Jun 27 '08, 06:50 PM

Re: Unique constraint and NULL values

"Agoston Bejo" <gusz1@freemail .huwrote in message
news:cl8ba7$d04 $1@news.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.
|
|

did you try a standard UNIQUE constraint on the column?

unlike SQL-Server (unless they've changed it since I last worked on it),
Oracle processes null values properly in this scenario (i.e., one NULL value
is never consider equal to another NULL value)

++ mcs

**Turkbear** · Jun 27 '08, 06:50 PM

Re: Unique constraint and NULL values

"Mark C. Stock" <mcstockX@Xenqu ery .comwrote:

>
>"Agoston Bejo" <gusz1@freemail .huwrote in message
>news:cl8ba7$d0 4$1@news.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.
>|
>|
>
>did you try a standard UNIQUE constraint on the column?
>
>unlike SQL-Server (unless they've changed it since I last worked on it),
>Oracle processes null values properly in this scenario (i.e., one NULL value
>is never consider equal to another NULL value)
>
>++ mcs
>

Yep..A Unique index can enforce a Unique Constraint and can handle NULLs ..A Primary key, on the other hand, cannot have
NULLs..

**Frank Piron** · Jun 27 '08, 06:50 PM

Re: Unique constraint and NULL values

Am Thu, 21 Oct 2004 09:37:42 -0400 schrieb Mark C. Stock
<mcstockX@Xenqu ery .com>:

>
>
did you try a standard UNIQUE constraint on the column?
>
unlike SQL-Server (unless they've changed it since I last worked on it),
Oracle processes null values properly in this scenario (i.e., one NULL
value
is never consider equal to another NULL value)

Yes, but it's a matter of convention because "NULL <NULL" evaluates
to null and thus is also not true. So two null values should never
be considered different.

++ mcs
>
>

--
Frank Piron,
etfrankatkonadd otn
(leftrotate two)

**Agoston Bejo** · Jun 27 '08, 06:50 PM

Re: Unique constraint and NULL values

See the answer below.

"Mark C. Stock" <mcstockX@Xenqu ery .comwrote in message
news:zsqdnU_Qrs leJurcRVn-vA@comcast.com. ..

"Agoston Bejo" <gusz1@freemail .huwrote in message
news:cl8ba7$d04 $1@news.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.
|
|
>
did you try a standard UNIQUE constraint on the column?

Yes, I did, and (1, null) and (1, null) couldn't be inserted twice. The
Oracle version I'm currently using (or to be more exact forced to use) is
8.1.7. Maybe in later versions this was corrected, I don't know. Here, when
I tried it, it worked the way I described in my original post.

>
unlike SQL-Server (unless they've changed it since I last worked on it),
Oracle processes null values properly in this scenario (i.e., one NULL

value

is never consider equal to another NULL value)
>
++ mcs
>
>

**Mark C. Stock** · Jun 27 '08, 06:50 PM

Re: Unique constraint and NULL values

"Agoston Bejo" <gusz1@freemail .huwrote in message
news:cl8jd0$f4p $1@news.caesar. elte.hu...
| See the answer below.
|
| "Mark C. Stock" <mcstockX@Xenqu ery .comwrote in message
| news:zsqdnU_Qrs leJurcRVn-vA@comcast.com. ..
| "Agoston Bejo" <gusz1@freemail .huwrote in message
| news:cl8ba7$d04 $1@news.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.
| |
| |
| >
| did you try a standard UNIQUE constraint on the column?
|
|
| Yes, I did, and (1, null) and (1, null) couldn't be inserted twice. The
| Oracle version I'm currently using (or to be more exact forced to use) is
| 8.1.7. Maybe in later versions this was corrected, I don't know. Here,
when
| I tried it, it worked the way I described in my original post.
|
| >
| unlike SQL-Server (unless they've changed it since I last worked on it),
| Oracle processes null values properly in this scenario (i.e., one NULL
| value
| is never consider equal to another NULL value)
| >
| ++ mcs
| >
| >
|
|

well, that's different than what you posted -- you want to enforce a
constraint on a pair of columns, not on a single column

what i've done in cases like this is create a 'shadow table' (my term, not
oracle's) that contains the two values, plus the primary key, just for rows
where both values are NOT NULL -- i put the 2-column UNIQUE constraint on
the shadow table instead of the original table, and then use a DML trigger
on the original table to maintain the shadow table -- any constraint
violations on the shadow table will propagate (through the trigger) to the
original table

(a variation of this technique also allows DRI across database links,
assuming the links are reliable)

++ mcs

**Mark C. Stock** · Jun 27 '08, 06:50 PM

Re: Unique constraint and NULL values

"Frank Piron" <empty@zero.nil wrote in message
news:opsf74act8 m0et4w@news.onl ine.de...
| Am Thu, 21 Oct 2004 09:37:42 -0400 schrieb Mark C. Stock
| <mcstockX@Xenqu ery .com>:
| >
| >
| did you try a standard UNIQUE constraint on the column?
| >
| unlike SQL-Server (unless they've changed it since I last worked on it),
| Oracle processes null values properly in this scenario (i.e., one NULL
| value
| is never consider equal to another NULL value)
|
| Yes, but it's a matter of convention because "NULL <NULL" evaluates
| to null and thus is also not true. So two null values should never
| be considered different.
|
| ++ mcs
| >
| >
|
| --
| Frank Piron,
| etfrankatkonadd otn
| (leftrotate two)

yes, in theory, the SQL Server approach is more 'pure', but in practice, the
Oracle approach is more practical (i'm starting to sound like yogi berra!)

if i'm creating a UNIQUE constraint, my goal is to disallow values that are
known to be equal to other known values -- so, it's entirely appropriate to
not disallow (sorry for the double-negs) unknown values (triple-negs?) since
there is no known basis for excluding them

++ mcs

**ctcgag@hotmail.com** · Jun 27 '08, 06:50 PM

Re: Unique constraint and NULL values

"Mark C. Stock" <mcstockX@Xenqu ery .comwrote:

yes, in theory, the SQL Server approach is more 'pure', but in practice,
the Oracle approach is more practical (i'm starting to sound like yogi
berra!)
>
if i'm creating a UNIQUE constraint, my goal is to disallow values that
are known to be equal to other known values -- so, it's entirely
appropriate to not disallow (sorry for the double-negs) unknown values
(triple-negs?) since there is no known basis for excluding them

But then why does it exclude them when there are other columns present?

Xho

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

**Turkbear** · Jun 27 '08, 06:50 PM

Re: Unique constraint and NULL values

"Agoston Bejo" <gusz1@freemail .huwrote:

>See the answer below.
>
>"Mark C. Stock" <mcstockX@Xenqu ery .comwrote in message
>news:zsqdnU_Qr sleJurcRVn-vA@comcast.com. ..

>"Agoston Bejo" <gusz1@freemail .huwrote in message
>news:cl8ba7$d0 4$1@news.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.
>|
>|
>>
>did you try a standard UNIQUE constraint on the column?

>
>
>Yes, I did, and (1, null) and (1, null) couldn't be inserted twice. The
>Oracle version I'm currently using (or to be more exact forced to use) is
>8.1.7. Maybe in later versions this was corrected, I don't know. Here, when
>I tried it, it worked the way I described in my original post.
>

>>
>unlike SQL-Server (unless they've changed it since I last worked on it),
>Oracle processes null values properly in this scenario (i.e., one NULL

>value

>is never consider equal to another NULL value)
>>
>++ mcs
>>
>>

>

A Unique Index only allows for 1 NULL in each of the indexed fields..So
Insert 1,NULL
and
insert 1,NULL

would violate the unique index since the NULL in field2 is the second NULL and is not allowed..
You could do a
insert NULL,1 without a problem ( except now both fields have their max NULLs, so no more will be allowed)

**Mark C. Stock** · Jun 27 '08, 06:50 PM

Re: Unique constraint and NULL values

"Turkbear" <john.g@dot.spa mfree.comwrote in message
news:1098379134 .J+ht36vV5NqDSC hGoHyPcg@terane ws...
| "Agoston Bejo" <gusz1@freemail .huwrote:
|
| >See the answer below.
| >
| >"Mark C. Stock" <mcstockX@Xenqu ery .comwrote in message
| >news:zsqdnU_Qr sleJurcRVn-vA@comcast.com. ..
| >"Agoston Bejo" <gusz1@freemail .huwrote in message
| >news:cl8ba7$d0 4$1@news.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.
| >|
| >|
| >>
| >did you try a standard UNIQUE constraint on the column?
| >
| >
| >Yes, I did, and (1, null) and (1, null) couldn't be inserted twice. The
| >Oracle version I'm currently using (or to be more exact forced to use) is
| >8.1.7. Maybe in later versions this was corrected, I don't know. Here,
when
| >I tried it, it worked the way I described in my original post.
| >
| >>
| >unlike SQL-Server (unless they've changed it since I last worked on
it),
| >Oracle processes null values properly in this scenario (i.e., one NULL
| >value
| >is never consider equal to another NULL value)
| >>
| >++ mcs
| >>
| >>
| >
| A Unique Index only allows for 1 NULL in each of the indexed fields..So
| Insert 1,NULL
| and
| insert 1,NULL
|
| would violate the unique index since the NULL in field2 is the second
NULL and is not allowed..
| You could do a
| insert NULL,1 without a problem ( except now both fields have their max
NULLs, so no more will be allowed)

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):
-----------------------------------------------------------------
SQLcreate table uk_demo (
2 id number constraint uk_demo$pk primary key
3 , name varchar2(30) constraint uk_demp$uk unique
4 );

Table created.

SQLinsert into uk_demo values (1,null);

1 row created.

SQLinsert into uk_demo values (2,null);

1 row created.

SQLinsert into uk_demo values (3,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.

SQLinsert into uk_demo2 values(1,200,nu ll);

1 row created.

SQLinsert into uk_demo2 values(2,200,nu ll);
insert into uk_demo2 values(2,200,nu ll)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UK_DEMO2 $UK) violated

but this is (multi-column unique constraint, all columns null for multiple
rows):
-----------------------------------------------------------------
SQLinsert into uk_demo2 values(3,null,n ull);

1 row created.

SQLinsert into uk_demo2 values(4,null,n ull);

1 row created.

++ mcs

**David Portas** · Jun 27 '08, 06:50 PM

Re: Unique constraint and NULL values

yes, in theory, the SQL Server approach is more 'pure'

Not really. The SQL standard consistently defines all the nullable
constraints (CHECK, UNIQUE, FOREIGN KEY) in the negative. In other words,
the constraint doesn't permit violations (logical FALSE) but nor does it
require that the constraint be satisfied (logical TRUE). The UNKNOWN case is
not considered a violation of a constraint, which is why NULLs are
permitted. Unfortunately Microsoft disagrees ;-)

--
David Portas
SQL Server MVP
--

**DA Morgan** · Jun 27 '08, 06:51 PM

Re: Unique constraint and NULL values

Frank Piron wrote:

Yes, but it's a matter of convention because "NULL <NULL" evaluates
to null and thus is also not true. So two null values should never
be considered different.
>

>++ mcs

Two nulls should never be evaluated ... there is nothing to consider.
So considering them the same or different is irrelevant.

CREATE TABLE t (
col1 NUMBER(1),
col2 VARCHAR2(1),
col3 DATE);

INSERT INTO t (col1) VALUES (1);
COMMIT;

What sense would it make to compare col2 and col3?
Would you consider them the same?
--
Daniel A. Morgan
University of Washington
damorgan@x.wash ington.edu
(replace 'x' with 'u' to respond)

**Frank Piron** · Jun 27 '08, 06:51 PM

Re: Unique constraint and NULL values

Thu, 21 Oct 2004 11:40:48 -0400 Mark C. Stock <mcstockX@Xenqu ery .com>
wrote:

>
yes, in theory, the SQL Server approach is more 'pure', but in practice,
the
Oracle approach is more practical (i'm starting to sound like yogi
berra!)

Agreed. IMO the most plausible argument for the Oracle approach
is the observation that allowing exactly one row with a null value
in a certain column would mean that
1)
The column value is "identifyin g" the row (unique constraint).
2)
NULL - an undefined value - is an "identifyin g" value.

which does not make sense for me.
--
Frank Piron,
etfrankatkonadd otn
(leftrotate two)

**David Portas** · Jun 27 '08, 06:51 PM

Re: Unique constraint and NULL values

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?

--
David Portas
SQL Server MVP
--

**Frank Piron** · Jun 27 '08, 06:51 PM

Re: Unique constraint and NULL values

Thu, 21 Oct 2004 18:54:29 -0700 DA Morgan <damorgan@x.was hington.edu>
wrote:

Two nulls should never be evaluated ... there is nothing to consider.
So considering them the same or different is irrelevant.
>
CREATE TABLE t (
col1 NUMBER(1),
col2 VARCHAR2(1),
col3 DATE);
>
INSERT INTO t (col1) VALUES (1);
COMMIT;
>
What sense would it make to compare col2 and col3?
Would you consider them the same?

No. I wanted to stress the fact that
eval(NULL=NULL) "=" eval(NULL<>NULL ) "=" "NULL"
where the doublequotes indicate metalanguage.

Unlike true and false, NULL acts on both levels
because it can be a column's "value" and can be
the result of a logical expression.

--
Frank Piron,
etfrankatkonadd otn
(leftrotate two)

Unique constraint and NULL values

Unique constraint and NULL values

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment