[FIXED] JdbcSQLIntegrityConstraintViolationException when inserting new entry after schema generated from class and initial data loaded from file

Issue

I have a spring boot project with an h2 database.

I have an entity class from which the schema is to be generated:

@NoArgsConstructor
@Entity
@Table(name = "NAMES")
public class Name {

    @Id
    @GeneratedValue
    public Long id;

    @Column(nullable = false)
    public String name;

    public Name(String name) {
        this.name = name;
    }

}

And I have a data.sql file:

insert into names (id, name) values (1, 'Alex');
insert into names (id, name) values (2, 'Bob');

my application.properties is:

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.defer-datasource-initialization=true
spring.jpa.show-sql=true

The application starts up just fine, I can confirm with localhost:8080/h2-console that the data is loaded into the database. But I cannot save new data into the table with

//public interface NameRepository extends CrudRepository<Name,Long> {}

@RestController
@Service
public class MyController {

  @Autowired
  private final NameRepository nameRepository;

  @PostMapping("/triggerError")
  public ResponseEntity<Void> trigger() {

    Name newName = new Name("Chris");
    nameRepository.save(newName);
    return ResponseEntity.ok().build();
  }
}

The error message is:

could not execute statement; SQL [n/a]; constraint [\"PRIMARY KEY ON PUBLIC.NAMES(ID) ( /* key:1 */ CAST(1 AS BIGINT), 'Alex')\"; SQL statement:
insert into names (name, id) values (?, ?) [23505-210]];

I assume that this means that spring wants to insert the new name at id=1, not realizing that ids 1 and 2 are already in use. I guess the right parameters to @GeneratedValue could fix it but I do not understand what they mean and which one to choose.

Trial and error:

@GeneratedValue(strategy = GenerationType.AUTO) is the default, see above.
@GeneratedValue(strategy = GenerationType.TABLE) same error
@GeneratedValue(strategy = GenerationType.SEQUENCE) same error
@GeneratedValue(strategy = GenerationType.IDENTITY) different error:

...
Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column \"ID\"; SQL statement:\ninsert into names (id, name) values (null, ?) [23502-210]
...
could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

So apparently it is not the annotation, but something else.

I'm giving up, heres my MRE: https://github.com/timo-a/duckpond-spring-backend/tree/debug/saving

Solution

@Shankar Ghimire is partially correct. This is both a Hibernate bug and a data.sql issue. Update your Hibernate version to 5.6.5:

implementation('org.springframework.boot:spring-boot-starter-data-jpa'){
  exclude group: 'org.hibernate', module: 'hibernate-core'
}
implementation 'org.hibernate:hibernate-core:5.6.5.Final'

and since you are using the h2 auto_increment, you should remove the id in your data.sql

insert into names (name)
values ('Alex'),('Bob');

The sequence won't increment otherwise leading to Unique index or primary key violation.

---

By the way, your repository uses Integer as your PK whereas your entity uses Long. And since the question is tagged as JPA, use :

public interface NameRepository extends JpaRepository<Name,Long> {}

And you insert data in a table named "scores" but I assume it's a typo.

Answered By - Lookslikeitsnot
Answer Checked By - Terry (JavaFixing Volunteer)