Re: Unique constraint over 2 columns with allowable NULLs

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 06 Dec 2007 07:34:43 +0100
Message-ID: <47579803.504@gmail.com>

vjayprabhu_at_gmail.com schrieb:

> On Nov 30, 5:09 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:

>> mikew01 schrieb:
>>
>>
>>
>>
>>
>>> Thanks for the replies, but in both cases you have put different data
>>> into the non null column so the uniqueness constraint has not been
>>> violated.
>>> Following on from the previous example...
>>> SQL> CREATE TABLE t (
>>> 2 col1 NUMBER,
>>> 3 col2 NUMBER);
>>> Table created.
>>> SQL> ALTER TABLE t
>>> 2 ADD CONSTRAINT uc_t
>>> 3 UNIQUE (col1, col2)
>>> 4 USING INDEX;
>>> Table altered.
>>> SQL> INSERT INTO t VALUES (1, NULL);
>>> 1 row created.
>>> What might happen is this again
>>> SQL> INSERT INTO t VALUES (1, NULL);
>>> Which will fail with Oracle
>>> Ive tried using a trigger to check the values when they are being
>>> updated but I get a mutating table error.
>> create unique index t_uidx on
>> t(nvl2(col1+col2,col1,null),nvl2(col1+col2,col2,null));
>>
>> Best regards
>>
>> Maxim- Hide quoted text -
>>
>> - Show quoted text -

> 
> hey Maxim,
> 
> Could you explain what you are doing here when creating the index? Has
> you tried creating it or is this more of a logical solution you seem
> to be giving?
> 
> Regards,
> 
> VJ

As far as i understood, the OP requirement is to implement an integrity constraint which follows the rules:
1) As long as both columns are not null, they should be unique 2) If any of both columns is NULL, non unique values should be allowed. If my interpretation is correct, suggested index should simulate this kind of constraint.
Just curiious, why are you asking about - do you have another understanding of OP requierements, or , do you think, this index is not supposed to work the way i described?

Best regards

Maxim Received on Thu Dec 06 2007 - 00:34:43 CST