Re: Unique constraint and NULL values

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Thu, 21 Oct 2004 14:46:11 -0400
Message-ID: <qYGdnZnKJ8CSmeXcRVn-sw@comcast.com>

"Turkbear" <john.g_at_dot.spamfree.com> wrote in message news:1098379134.J+ht36vV5NqDSChGoHyPcg_at_teranews...
| "Agoston Bejo" <gusz1_at_freemail.hu> wrote:
|
| >See the answer below.
| >
| >"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
| >news:zsqdnU_QrsleJurcRVn-vA_at_comcast.com...
| >> "Agoston Bejo" <gusz1_at_freemail.hu> wrote in message
| >> news:cl8ba7$d04$1_at_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 Received on Thu Oct 21 2004 - 13:46:11 CDT