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

i run h2 in cluster mode 2 nodes.

i have 2 tables. parent, , child. child contains foreign key id of row of parent table.
i'm experiencing weird issue can not understand : working ok until violate unique constraint.

steps:
- working normally
- violate (by purpose here) unique constraint
- when adding child rows, referential integrity violation on foreign key (parent.id), child row added.

script:

create table child(id int auto_increment, name varchar(255), fkey int); create table parent(id int auto_increment, name varchar(255) unique);  alter table `child` add foreign key (fkey) references `parent` (`id`)  on delete cascade;  -- insert first parent, id '1'. insert child, works. insert parent(name) values('parent1'); insert child(name, fkey) values('child1', 1);   -- purpose, violate unique constraint violation on parent.name : unique index or primary key violation: "constraint_index_8 on public.parent(name) values ( /* 2 */ 'parent1' )" insert parent(name) values('parent1');  -- delete parent (by cascade, childs deleted) delete parent name='parent1';  -- re-insert parent, create row (3, "parent3") insert parent(name) values('parent3');  -- try insert child parent '3',  -- referential integrity constraint violation: "constraint_3: public.child foreign key(fkey) references public.parent(id) (3)" insert child(name, fkey) values('child3', 3);  -- child propertly added ! 

the workaround found replace last insert into :

insert child(name, fkey) values('child3', select id parent name='parent3');  

but weird, because select id parent name='parent3' returns 3.

it happens when using sequences instead of auto_increment.
does not happens when don't use auto_increment or sequences , when manage id myself (but don't want to).
it not happens when run h2 without cluster. tested h2 v1.3.176 , h2 v1.4.189.

can explain ? did make mistake here ?

h2 main developper thomas mueller answered question on github:

this 1 of documented limitations of cluster feature, see "clustering algorithm , limitations": "using auto-increment , identity columns not supported."

i'm afraid it's hard fix it. suggest not use cluster feature reason. support removed in future. hope new cluster / automatic failover feature can added, take time.

but might interesting you: https://github.com/shesse/h2ha

see issue on github

i managed working: using sequences , inserting in 2 times:
- nextval table sequence, eg: nextid =select childsequence.nextval dual
- insert child , specify id nextid