Unique constraint violation error thrown when there are no conflicting rows

[GoogleCodeExporter]

I have a Java application using Hibernate 4.1 and H2 DB version 1.3.170. A 
false unique constraint violation error is reported when inserting into a table 
which has a single BIGINT primary key column and two additional columns (types: 
BOOLEAN and BIGINT) which are marked with a unique constraint, roughly:

create table test
(
   pk bigint not null
   flag boolean not null,
   business_id bigint not null,
   primary key (pk),
   unique (flag, business_id)
);

When Hibernate starts inserting records then it reports a unique constraint 
violation on the 2-column unique constraint (and not on the primary key). The 
table is connected with quite a couple foreign key relations to other tables.

However, when I remove the unique constraint, let Hibernate insert the records 
and check the DB there are no conflicting rows. Even more strange is that when 
I open the DB in H2 console and add a unique index on the two columns (flag, 
business_id) manually, then the constraint is added, so the problem is not in 
the application trying to insert conflicting rows.

The issue comes up both in in-memory and in file-based DBs.

What steps will reproduce the problem?
Unfortunately, I was unable to come up with a minimum test case to reproduce 
the issue.

What is the expected output? What do you see instead?
No errors are expected as there are no conflicting rows.

What version of the product are you using? On what operating system, file
system, and virtual machine?
H2 version: 1.3.170
OS: Ubuntu Linux (kernel: 3.2.0-23-generic)

Do you know a workaround?

The only workaround is to remove the unique constraint on the two columns.

Original issue reported on code.google.com by krisztia...@gmail.com on 27 Feb 2013 at 10:02

[GoogleCodeExporter]

This is most likely a problem with how you have configured Hibernate to 
generate unique keys.

You should start by asking questions on the hibernate mailing list.

Original comment by noelgrandin on 4 Mar 2013 at 5:59

[GoogleCodeExporter]

In the meantime I've discovered the problem: I've used @NaturalId(mutable = 
true) annotation in the Hibernate mapping and started inserting records. 
However, instead of inserting the correct natural IDs in one SQL, Hibernate 4 
somehow decides to insert the record first with the default values then run a 
bunch of updates to update the natural ID columns. Obviously, in case of more 
than one records this yields the unique constraint violation.

Unfortunately, there's no way to tell Hibernate to stop doing that nor there's 
a way to instruct H2 to defer the checking of unique constraints at the end of 
the transaction.

Closing the issue as it's indeed not an H2 bug. Sorry for the inconveniences!

Original comment by krisztia...@gmail.com on 4 Mar 2013 at 7:47

[GoogleCodeExporter]

Original comment by noelgrandin on 6 Mar 2013 at 5:05

• Changed state: Invalid