By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

Unique constraint and NULL values

P: n/a
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.
Jul 19 '05 #1
Share this Question
Share on Google+
26 Replies


P: n/a

"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
Jul 19 '05 #2

P: n/a
"Mark C. Stock" <mcstockX@Xenquery .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..

Jul 19 '05 #3

P: n/a
Am Thu, 21 Oct 2004 09:37:42 -0400 schrieb Mark C. Stock
<mcstockX@Xenquery .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,
etfrankatkonaddotn
(leftrotate two)
Jul 19 '05 #4

P: n/a
See the answer below.

"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
news:zs********************@comcast.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

Jul 19 '05 #5

P: n/a

"Agoston Bejo" <gu***@freemail.hu> wrote in message
news:cl**********@news.caesar.elte.hu...
| See the answer below.
|
| "Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
| news:zs********************@comcast.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
Jul 19 '05 #6

P: n/a

"Frank Piron" <em***@zero.nil> wrote in message
news:op**************@news.online.de...
| Am Thu, 21 Oct 2004 09:37:42 -0400 schrieb Mark C. Stock
| <mcstockX@Xenquery .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,
| etfrankatkonaddotn
| (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


Jul 19 '05 #7

P: n/a
"Mark C. Stock" <mcstockX@Xenquery .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
Jul 19 '05 #8

P: n/a
"Agoston Bejo" <gu***@freemail.hu> wrote:
See the answer below.

"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
news:zs********************@comcast.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)


Jul 19 '05 #9

P: n/a

"Turkbear" <jo****@dot.spamfree.com> wrote in message
news:1098379134.J+ht36vV5NqDSChGoHyPcg@teranews...
| "Agoston Bejo" <gu***@freemail.hu> wrote:
|
| >See the answer below.
| >
| >"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
| >news:zs********************@comcast.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,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
but this is (multi-column unique constraint, all columns null for multiple
rows):
-----------------------------------------------------------------
SQL> insert into uk_demo2 values(3,null,null);

1 row created.

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

1 row created.
++ mcs
Jul 19 '05 #10

P: n/a
> 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
--
Jul 19 '05 #11

P: n/a
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
da******@x.washington.edu
(replace 'x' with 'u' to respond)
Jul 19 '05 #12

P: n/a
David Portas wrote:
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 ;-)


Actually that isn't entirely true. SQL Server, in order to claim ANSI
compliance has a parameter that allows it to work as does Oracle. It
just isn't the default. And since most SQL Server types can't or don't
read manuals I can't think of a single instance where I've seen it set
for ANSI compliance.

--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace 'x' with 'u' to respond)
Jul 19 '05 #13

P: n/a
Thu, 21 Oct 2004 11:40:48 -0400 Mark C. Stock <mcstockX@Xenquery .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 "identifying" the row (unique constraint).
2)
NULL - an undefined value - is an "identifying" value.

which does not make sense for me.
--
Frank Piron,
etfrankatkonaddotn
(leftrotate two)
Jul 19 '05 #14

P: n/a
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
--
Jul 19 '05 #15

P: n/a
Thu, 21 Oct 2004 18:54:29 -0700 DA Morgan <da******@x.washington.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,
etfrankatkonaddotn
(leftrotate two)
Jul 19 '05 #16

P: n/a
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.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace 'x' with 'u' to respond)
Jul 19 '05 #17

P: n/a
Frank Piron wrote:
Thu, 21 Oct 2004 18:54:29 -0700 DA Morgan <da******@x.washington.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.


Demos of this can be found at http://www.psoug.org
Click on Morgan's Library
Click on NULL (on the web 'NULL' has a value)

--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace 'x' with 'u' to respond)
Jul 19 '05 #18

P: n/a
Am Fri, 22 Oct 2004 08:33:52 -0500 schrieb Turkbear
<jo****@dot.spamfree.com>:
It can get almost metaphysical, but:
Identifying a column which has an undeterminable value ( NULL) is not
the same as identifying that value, so being able to
consider that column unique ( that is , it is the only one with the
undeterminable value) is not a problem.


The difference you point to is clear for me.
But i did not find a real world example for
a column which is unique in a table and at
most one row with a NULL value for this column
should be allowed.

--
Frank Piron,
etfrankatkonaddotn
(leftrotate two)
Jul 19 '05 #19

P: n/a
If you want to get around this, then use a function-based index to
avoid including in a unique index any rows where either of the columns
is null.

One method would be ...
Expand|Select|Wrap|Line Numbers
  1. Create unique index my_ind
  2. on my_tab (Decode(col2,Null,Null,col1),Decode(col1,Null,Null,col2))
  3.  
Jul 19 '05 #20

P: n/a
"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
//
//
Jul 19 '05 #21

P: n/a

"Walt" <wa*********@YourShoesyahoo.com> wrote in message
news:41***************@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)
Jul 19 '05 #22

P: n/a
DA Morgan <da******@x.washington.edu> wrote in message news:<1098427498.727456@yasure>...
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
Jul 19 '05 #23

P: n/a
Walt <wa*********@YourShoesyahoo.com> wrote:
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?


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.
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?
There isn't a one-null-per-column allowance. I can put as many nulls
as I want into one of the columns, as long as each is paired with a
different value for the other column.
Paraphrasing Frank Piron
(above) , it seems that Oracle is treating null as an identifying value,
which doesn't make sense to me.


Xho

[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
Jul 19 '05 #24

P: n/a
ct****@hotmail.com wrote:
Walt <wa*********@YourShoesyahoo.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.

[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


And it may very well be implemented as a string concatenation at some
low level. As you suggest, the behavior is the same. And if it walks
like a duck...

--
//-Walt
//
//
Jul 19 '05 #25

P: n/a
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" <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.

Jul 19 '05 #26

P: n/a
"hankr" <ha***@skillview.com> wrote in message news:<co********@enews1.newsguy.com>...
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" <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.


Mark Stock presented the answer that same day (ah, the occasional
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
Jul 19 '05 #27

This discussion thread is closed

Replies have been disabled for this discussion.