Re: Unique constraint over 2 columns with allowable NULLs

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 Received on Fri Nov 30 2007 - 07:09:44 CST