Unique key constraint enforcement fails on null values.

[GoogleCodeExporter]

Environment:
  - H2 version: 1.3.161 (installed from maven repository)
  - Output of "java -version":

java version "1.6.0_23"
OpenJDK Runtime Environment (IcedTea6 1.11pre) (6b23~pre11-0ubuntu1.11.10.1)
OpenJDK Server VM (build 20.0-b11, mixed mode)

Sql to reproduce:

create table barfoo (
    id int, foo int, bar int,
    primary key (id),
    unique (foo, bar)
);

insert into barfoo (id) values (1)
insert into barfoo (id) values (2)

--

The second insert succeeds and creates the row while it should fail with 
constraint violation. I tried this also with different combination null and 
non-null values - the constraint works only if all values are non-nulls.

Original issue reported on code.google.com by timo.san...@teknokala.com on 14 Feb 2012 at 12:48

[GoogleCodeExporter]

For questions, please use StackOverflow or the Google Group as written on the 
H2 home page.

This is not a bug. Most databases work like this (including MySQL, PostgreSQL, 
HSQLDB), except Apache Derby.

drop table barfoo;
create table barfoo (
       id int, foo int, bar int,
       primary key (id)
);
create unique index idx_bar_foo on barfoo(foo, bar);
insert into barfoo (id) values (1);
insert into barfoo (id) values (2);

Original comment by thomas.t...@gmail.com on 17 Feb 2012 at 5:45

• Changed state: Invalid