Re: Unique constraint over 2 columns with allowable NULLs

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.

Cheers

On 29 Nov, 16:50, DA Morgan <damor..._at_psoug.org> wrote:
> mikew01 wrote:
> > Hello, I need to add a unique constraint over 2 columns where one of
> > the columns could be NULL.
> > A standard UNIQUE constraint applied over these 2 columns will break
> > when someone tries to put a second NULL into the allowable NULL column
> > so Im wondering how to go about enforcing this constraint?
>
> > TIA
>
> 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.
>
> SQL> INSERT INTO t VALUES (2, NULL);
>
> 1 row created.
>
> SQL> INSERT INTO t VALUES (3, NULL);
>
> 1 row created.
>
> SQL> COMMIT;
>
> Commit complete.
>
> SQL>
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org
Received on Fri Nov 30 2007 - 05:26:06 CST