Re: Unique constraint over 2 columns with allowable NULLs

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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org