Unique constraint and NULL values

[Agoston Bejo]

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.

[Mark C. Stock]

"Agoston Bejo" <gu***@freemail .hu> wrote in message
news:cl******** **@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]

"Mark C. Stock" <mcstockX@Xenqu ery .com> wrote:

"Agoston Bejo" <gu***@freemail .hu> wrote in message
news:cl******* ***@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]

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]

See the answer below.

"Mark C. Stock" <mcstockX@Xenqu ery .com> wrote in message
news:zs******** ************@co mcast.com...

"Agoston Bejo" <gu***@freemail .hu> wrote in message
news:cl******** **@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]

"Agoston Bejo" <gu***@freemail .hu> wrote in message
news:cl******** **@news.caesar. elte.hu...
| See the answer below.
|
| "Mark C. Stock" <mcstockX@Xenqu ery .com> wrote in message
| news:zs******** ************@co mcast.com...
| > "Agoston Bejo" <gu***@freemail .hu> wrote in message
| > news:cl******** **@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]

"Frank Piron" <em***@zero.nil > wrote in message
news:op******** ******@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]

"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!)

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]

"Agoston Bejo" <gu***@freemail .hu> wrote:

See the answer below.

"Mark C. Stock" <mcstockX@Xenqu ery .com> wrote in message
news:zs******* *************@c omcast.com...

"Agoston Bejo" <gu***@freemail .hu> wrote in message
news:cl******** **@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]

"Turkbear" <jo****@dot.spa mfree.com> wrote in message
news:1098379134 .J+ht36vV5NqDSC hGoHyPcg@terane ws...
| "Agoston Bejo" <gu***@freemail .hu> wrote:
|
| >See the answer below.
| >
| >"Mark C. Stock" <mcstockX@Xenqu ery .com> wrote in message
| >news:zs******* *************@c omcast.com...
| >> "Agoston Bejo" <gu***@freemail .hu> wrote in message
| >> news:cl******** **@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):
-----------------------------------------------------------------
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.

SQL> insert 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.

SQL> insert into uk_demo2 values(1,200,nu ll);

1 row created.

SQL> insert 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):
-----------------------------------------------------------------
SQL> insert into uk_demo2 values(3,null,n ull);

1 row created.

SQL> insert into uk_demo2 values(4,null,n ull);

1 row created.
++ mcs