"JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation" Exception after upgrading to Spring Boot 2.7

user_6661361 0
Viewed 74

After upgrading my project to Spring Boot 2.7, my tests started failing with this error:

ERROR   ---[           main] o.h.engine.jdbc.spi.SqlExceptionHelper   :Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.MODEL(ID) ( /* key:1 */ CAST(1 AS BIGINT), 'Model 1 Description')"; SQL statement:
insert into model (id, description) values (default, ?, ?, ?, ?, ?, ?, ?) [23505-212]

This is what I have in my is my data.sql to pre-load the data for my tests using H2:

INSERT INTO Model(id, description) VALUES (1, 'Model 1 Description');

This is my Entity:

@Entity
public class Model {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String description;

    // ...

And the error is triggered when this test is executed:

@Test
void whenModelCreated_thenSuccess() {
    Model1 newModel = new Model("First Test Model");
    modelRepository.save(newModel);
    
    // ...
}
spring-data-jpa spring-boot h2 hibernate
1 Answers

It seems Boot 2.7 upgraded to its H2 dependency to 2.x, which is not backward compatible and introduces several changes: https://github.com/spring-projects/spring-boot/wiki/Spring-Boot-2.7-Release-Notes#h2-21

As indicated in the H2 "migration-to-v2" guide, there have been changes in how identity columns and sequences are handled. Apparently, the H2 database engine has to manage all the generated ids in order to keep track of them, and by indicating the id to be inserted in the DB explicitly, we're bypassing the database engine, and generating issues.

Changing my data.sql INSERT statement to use the default keyword to leave it up to the DB engine to take care of assigning the id itself, the isso got fixed:

INSERT INTO Model(id, description) VALUES (default, 'Model 1 Description');
user_66613610
Related