Adam wrote:

> Answering his own question:
> ...
> CONSTRAINT UQ_test UNIQUE (a,b,c)
> INSERT INTO TEST (ID, A, B, C) VALUES (1, 1, 1, NULL);
> INSERT INTO TEST (ID, A, B, C) VALUES (2, 1, 1, NULL);
>
> -- above line violates unique constraint.
>
> So:
>

OK, that's the way 1.5.x works and probably 1.0.x. However,
it's not standard and not the way V2 works. In V2, you can
have any number of nulls in a unique key and nulls do not
match other nulls. You can have two (or more) rows that are
all null. However, if you have a mix of nulls and values,
the combination of values must be unique.


create table abc (a integer, b integer, c integer);
alter table abc add constraint abc_unique unique (A, B, C);
commit;
insert into abc (a, b) values (1, 2);
insert into abc (a, c) values (1, 2);
insert into abc (a, c) values (1, 3);
insert into abc values (null, null, null);
insert into abc values (null, null, null);
insert into abc (a) values (1);
select * from ABC;

A B C
======== ============ ============

1 2 <null>
1 <null> 2
1 <null> 3
<null> <null> <null>
<null> <null> <null>
1 <null> <null>



SQL> insert into abc (a, b) values (1, 2);
Statement failed, SQLCODE = -803

violation of PRIMARY or UNIQUE KEY constraint "ABC_UNIQUE" on table "ABC"
SQL>


Regards,


Ann