Re: Unique constraint and NULL values

"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) Received on Thu Oct 21 2004 - 12:20:06 CDT