Re: Unique constraint over 2 columns with allowable NULLs

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 Received on Wed Dec 05 2007 - 18:10:43 CST