h2 does not correctly report the name of a violated unique constraint

[gavinking]

Given:

    create table Constrained (
        id bigint not null,
        count integer not null,
        name varchar(255),
        primary key (id),
        constraint count_name_key unique (count, name)
    )

A violation of the unique constraint is reported as:

Unique index or primary key violation: "PUBLIC.COUNT_NAME_KEY_INDEX_2 ON PUBLIC.CONSTRAINED(COUNT NULLS FIRST, NAME NULLS FIRST) VALUES ( /* key:1 */ 69, 'Gavin')"; SQL statement:
insert into Constrained(id,name,count) values (4,'Gavin',69) [23505-224]

But PUBLIC.COUNT_NAME_KEY_INDEX_2 is not the name of the constraint, it's a database-generated name. The name of the constraint is count_name_key.

I saw in another issue here that Hibernate was blamed for not correctly extracting the constraint name from h2 messages, but Hibernate cannot possibly extract the constraint name, because it is not contained in the message.

[lorenzbaier]

i have the same issue but it seems like it orignates from not even setting the constraint name in the DDL

hibernate generates: create table "point" ... unique ("number", "parent_version_identifier"), unique ("code", "parent_version_identifier")) instead of create table "point" ... constraint "UK1234566" unique ("number", "parent_version_identifier"), constraint "UK123456safseef6" unique ("code", "parent_version_identifier"))

[andreitokar]

Hibernate cannot possibly extract the constraint name, because it is not contained in the message.

IMHO, this is not the case, because constraint name is out there: PUBLIC.COUNT_NAME_KEY_INDEX_2 ON PUBLIC.CONSTRAINED(COUNT NULLS FIRST, NAME NULLS FIRST) VALUES ( / key:1 / 69, 'Gavin')"; SQL statement: insert into Constrained(id,name,count) values (4,'Gavin',69) [23505-224]

H2 reports unique key violation by using index name, not a constraint name. The reason being, that it might be no corresponding constraint at all. Nevertheless, if index was created automatically from the constraint, then it's name would bear a constraint name in it, followed by "_INDEX_"...

[gavinking]

@andreitokar

But that makes no sense from the perspective of the user. The user created a constraint named count_name_key. If the user wanted a constraint named COUNT_NAME_KEY_INDEX_2 they would have called it that.

H2 reports unique key violation by using index name, not a constraint name.

Yes, I know that. And that's ... bad.

From my perspective as a user, it makes interpreting and handling errors from JDBC more difficult, for no benefit.

Nevertheless, if index was created automatically from the constraint, then it's name would bear a constraint name in it, followed by "INDEX"

This is an implementation detail which I as a user just don't care about. I want to stay at the level of abstraction I'm working at.

If it were only a matter of the _INDEX_ bit, it would still be bad, but I could deal with it. The problem is that it includes an even worse 2 that for me is completely unpredictable/uninterpretable. Where is this number even coming from? Why is it 2 and not 0? Is it always going to be 2 or could it someday switch to 3?

[gavinking]

@lorenzbaier

i have the same issue but it seems like it orignates from not even setting the constraint name in the DDL

The name member of the JPA-standard @UniqueConstraint annotation lets you set the name of the unique constraint, which is what I did to tell Hibernate to generate the DDL you see in the issue description:

constraint count_name_key unique (count, name)

Of course, if you don't specify a name of the unique key, then Hibernate has to invent one for you.

[fheck]

The two DDL versions my colleague @lorenzbaier reported were generated by the same client code but different hibernate and H2 versions. We looked for a reason why we were not able to see the constraint name any more, which is why we stumbled over this issue. I apologize, I'm currently not a work until after the Easter holidays, so I can't report on the exact versions which show the described behavior, but this change in the DDL seems to be a change in a recent version, because before the upgrade we were able to extract the constrain name.

[katzyn]

The only correct way to get a constraint name from an index name in H2 is to query INFORMATION_SCHEMA.TABLE_CONSTRAINTS view. Index name is specified in non-standard INDEX_CATALOG, INDEX_SCHEMA, and INDEX_NAME columns. Constraint name is specified in standard CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, and CONSTRAINT_NAME columns.

Note: the same index may be used by multiple constraints, so a query with filter conditions on index name can return multiple rows.

Hypothetically we can try to report names of constrains instead, but indexes and constraints have different namespaces. It means we'll need to throw these exceptions with different error codes, one for violation of indexes used by constraints and another one for unique indexes without constraints.

[fheck]

So, I was able to determine the versions that generated the statements above: hibernate 6.4.4.Final/h2 2.2.224 generates: create table "point" ... unique ("number", "parent_version_identifier"), unique ("code", "parent_version_identifier"))
hibernate 6.2.13.Final/h2 2.2.214 generates create table "point" ... constraint "UK1234566" unique ("number", "parent_version_identifier"), constraint "UK123456safseef6" unique ("code", "parent_version_identifier")) with the same client code, so something in these updates seems to change how the DDL statement is generated, when the name of the unique constraint is not explicitly set.

[gavinking]

@fheck So it's correct in the latest release then.

[lorenzbaier]

@gavinking is there a changelog which reports this new behaviour of hibernate not generating the constraint names anymore?

[gavinking]

@gavinking is there a changelog which reports this new behaviour of hibernate not generating the constraint names anymore?

There is no such "new behavior".

I mean, in the original description of this issue I show DDL generated by Hibernate, clearly containing the constraint name.

[lorenzbaier]

@gavinking but the auto generated constraint names are obviously missing and i do not want to explicitly state them all

[gavinking]

I really don't know what you're asking about, since it's clear that Hibernate DDL generation is respecting all user-specified constraint names. And I don't know why you think the issue tracker for h2 is the appropriate place to discuss this.

Hibernate has a user forum for questions about Hibernate.

[lorenzbaier]

i created an issue on hibernate https://hibernate.atlassian.net/browse/HHH-17921