lmo lmo - 5 months ago 7x
SQL Question

H2 cluster weird behavior : fake referential Integrity violation on foreign key

I run H2 in cluster mode with 2 nodes.

I have two tables. A parent, and a child. The child contains a foreign key to the ID of a row of the parent table.

I'm experiencing a weird issue that I can not understand : everything is working OK until I violate a unique constraint.


- Everything is working normally

- I violate (by purpose here) a Unique constraint

- Now when adding child rows, I get a referential integrity violation on the foreign key (parent.id), but the child row is properly added.


create table CHILD(id int auto_increment, name varchar(255), fkey int);
create table PARENT(id int auto_increment, name varchar(255) UNIQUE);


-- Insert the first parent, id will be '1'. Then insert the child, this works.
insert into PARENT(name) values('parent1');
insert into CHILD(name, fkey) values('child1', 1);

-- By purpose, we violate the Unique constraint violation on PARENT.name : Unique index or primary key violation: "CONSTRAINT_INDEX_8 ON PUBLIC.PARENT(NAME) VALUES ( /* 2 */ 'parent1' )"
insert into PARENT(name) values('parent1');

-- Then I delete this parent (by cascade, all childs are deleted)
delete from PARENT where name='parent1';

-- Then I re-insert this parent, this create the row (3, "parent3")
insert into PARENT(name) values('parent3');

-- I try to insert a child with the parent '3',
-- I get a Referential integrity constraint violation: "CONSTRAINT_3: PUBLIC.CHILD FOREIGN KEY(FKEY) REFERENCES PUBLIC.PARENT(ID) (3)"
insert into CHILD(name, fkey) values('child3', 3);
-- But the child was propertly added !

The workaround I found is to replace the last
insert into
with :

insert into CHILD(name, fkey) values('child3', SELECT ID from parent where name='parent3');

But this is so weird, because
SELECT ID from parent where name='parent3'

It also happens when using sequences instead of auto_increment.

It does not happens when I don't use auto_increment or sequences and so when I manage the id myself (but I don't want to).

It does not happens when I run H2 without cluster.
Tested with H2 v1.3.176 and H2 v1.4.189.

Can somebody explain this ? Did I make a mistake here ?

lmo lmo

H2 main developper thomas mueller answered this question on github:

This is one of the documented limitations of the cluster feature, see also "Clustering Algorithm and Limitations": "Using auto-increment and identity columns is currently not supported."

I'm afraid it's hard to fix it. I suggest to not use the cluster feature for this reason. Support for it will probably be removed in the future. I hope a new cluster / automatic failover feature can be added, but this will take some time.

But this might be interesting for you: https://github.com/shesse/h2ha

See issue on github

I managed to get it working: using sequences and inserting in two times:
- Get the nextval for this table sequence, eg: nextid =select childsequence.nextval from dual
- Then do your INSERT INTO child and specify the id nextid