H2 - GENERATED BY DEFAULT AS IDENTITY conflicting with entries that have manually assigned IDs - Stack Overflow

admin管理员组

文章数量:1122846

Does H2 support mixing between auto generated ID's and INSERTs with explicitly defined ID's? Is there a way to make this work?

Here is a simple example:

DROP TABLE IF EXISTS test;

CREATE TABLE test(
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title VARCHAR(255)
);

INSERT INTO test(id, title) VALUES (1, 'hello'), (2, 'world');
INSERT INTO test(title) VALUES ('my new value');       -- THIS INSERT FAILS

The last insert fails with the following exception:

Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.TEST(ID) ( /* key:1 */ CAST(1 AS BIGINT), 'hello')"; SQL statement:!

It looks like the auto generation does not detect the existing ID's and tries to use the value 1 again. The documentation does not seem to state anything about this behaviour. It is a pretty common case to have some sql dumps with existing ID's somewhere. After importing these on a new DB, the application will always break. Am I missing something? Please help!

H2 versions tested: 2.2.224, 2.3.232 - both result in errors.

Does H2 support mixing between auto generated ID's and INSERTs with explicitly defined ID's? Is there a way to make this work?

Here is a simple example:

DROP TABLE IF EXISTS test;

CREATE TABLE test(
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title VARCHAR(255)
);

INSERT INTO test(id, title) VALUES (1, 'hello'), (2, 'world');
INSERT INTO test(title) VALUES ('my new value');       -- THIS INSERT FAILS

The last insert fails with the following exception:

Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.TEST(ID) ( /* key:1 */ CAST(1 AS BIGINT), 'hello')"; SQL statement:!

It looks like the auto generation does not detect the existing ID's and tries to use the value 1 again. The documentation does not seem to state anything about this behaviour. It is a pretty common case to have some sql dumps with existing ID's somewhere. After importing these on a new DB, the application will always break. Am I missing something? Please help!

H2 versions tested: 2.2.224, 2.3.232 - both result in errors.

• h2

Share Improve this question Follow asked Nov 22, 2024 at 13:39 maddobmaddob 1,00911 gold badge1313 silver badges3030 bronze badges 1

• 2 Generators of standard identity columns work in that way, see stackoverflow.com/questions/72402946/… for more details. – Evgenij Ryazanov Commented Nov 22, 2024 at 13:52

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

My mistake, it is indeed included in the documentation as Evgenij Ryazanov pointed out in the comments. I was able to fix the problem by starting the h2 in mode LEGACY, other modes will work as well. Information about different compatibility modes can be found here

本文标签： H2GENERATED BY DEFAULT AS IDENTITY conflicting with entries that have manually assigned IDsStack Overflow