alter table add foreign key (java.lang.NullPointerException)

[GoogleCodeExporter]

select * from information_schema.settings where name = 'info.VERSION';
info.VERSION    1.3.164 (2012-02-03)

--test1 (very well)
drop table if exists test;
create table test (id int not null primary key, pid int, constraint fk_test 
foreign key (pid) references test (id));
insert into test values (1, null);
insert into test values (2, null);
update test set pid = 1 where id = 2;

--test1 (very well)
drop table if exists test;
Update count: 0
(16 ms)

create table test (id int not null primary key, pid int, constraint fk_test 
foreign key (pid) references test (id));
Update count: 0
(0 ms)

insert into test values (1, null);
Update count: 1
(0 ms)

insert into test values (2, null);
Update count: 1
(0 ms)

update test set pid = 1 where id = 2;
Update count: 1
(0 ms)

--test2 (very bad)
drop table if exists test;
create table test (id int not null, pid int);
create unique index xpk_test on test (id);
alter table test add constraint pk_test primary key  (id) index xpk_test;
create index xfk_test on test (pid);
alter table test add constraint fk_test foreign key (pid) references test (id) 
index xfk_test;

insert into test values (1, null);
insert into test values (2, null);
update test set pid = 1 where id = 2;

--test2 (very bad)
drop table if exists test;
Update count: 0
(1 ms)

create table test (id int not null, pid int);
Update count: 0
(2 ms)

create unique index xpk_test on test (id);
Update count: 0
(0 ms)

alter table test add constraint pk_test primary key  (id) index xpk_test;
Update count: 0
(0 ms)

create index xfk_test on test (pid);
Update count: 0
(0 ms)

alter table test add constraint fk_test foreign key (pid) references test (id) 
index xfk_test;
Update count: 0
(5 ms)

insert into test values (1, null);
Update count: 1
(1 ms)

insert into test values (2, null);
Update count: 1
(0 ms)

update test set pid = 1 where id = 2;
General error: "java.lang.NullPointerException"; SQL statement:
update test set pid = 1 where id = 2 [50000-164] HY000/50000 

Original issue reported on code.google.com by victor.n...@gmail.com on 17 Feb 2012 at 2:30

[GoogleCodeExporter]

need to handle:
alter table test add constraint fk_test foreign key (pid) references test (id) 
index xfk_test;

must be:
alter table test add constraint fk_test foreign key (pid) references test (id) 
index xPk_test;

NullPointerException, is hard to understand :).

Original comment by victor.n...@gmail.com on 17 Feb 2012 at 3:20

[GoogleCodeExporter]

The "index xfk_..." at the end of the statement is not part of the official 
syntax - see http://h2database.com/html/grammar.html#referential_constraint

However there should be no exception of course.

Original comment by thomas.t...@gmail.com on 23 Feb 2012 at 7:19

• Changed state: Accepted

[GoogleCodeExporter]

Should be fixed in H2 version 1.3.165.

Original comment by thomas.t...@gmail.com on 18 Mar 2012 at 5:36

• Changed state: Fixed