Re: Unique constraint over 2 columns with allowable NULLs

Comments embedded.
On Nov 29, 9:36 am, mikew01 <mike..._at_blueyonder.co.uk> wrote:
> Hello, I need to add a unique constraint over 2 columns where one of
> the columns could be NULL.

I can't see what's stopping you from doing so:

SQL> create table uq_test (uq1 number, uq2 varchar2(12), other varchar2(40));

Table created.

SQL> alter table uq_test add constraint uq_test_uq unique(uq1, uq2);

Table altered.

SQL> insert into uq_test (uq1, uq2, other) values (1, null, 'test 1');

1 row created.

SQL> insert into uq_test (uq1, uq2, other) values (2, null, 'test 2');

1 row created.

SQL> select * from uq_test;

       UQ1 UQ2 OTHER

---------- ------------ ----------------------------------------
         1              test 1
         2              test 2

SQL>
> A standard UNIQUE constraint applied over these 2 columns will break
> when someone tries to put a second NULL into the allowable NULL column

Not true, as evidenced above.

> so Im wondering how to go about enforcing this constraint?
>
> TIA
David Fitzjarrell Received on Thu Nov 29 2007 - 10:41:33 CST