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.
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