AUTO_INCREMENT in H2 database doesn't work when requesting with Postman

Question

I want to persist TODOs in a H2 DB facilitating a Spring Boot application.

The following SQL script initializes the DB and it works properly:

DROP TABLE IF EXISTS todos;

CREATE TABLE todos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(250) NOT NULL,
  completion_date DATE,
  priority VARCHAR(6) CHECK(priority IN ('LOW', 'MEDIUM', 'HIGH'))
);

INSERT INTO todos (title, description, priority) VALUES
  ('Create xxx Todo', 'An xxx-TODO must be created.', 'HIGH'),
  ('Delete xxx Todo', 'An xxx-TODO must be deleted.', 'HIGH'),
  ('Update xxx Todo', 'An xxx-TODO must be updated.', 'MEDIUM'),
  ('Complete xxx Todo', 'An xxx-TODO must be completed.', 'LOW');

Console output when starting Spring Boot:

Hibernate: drop table if exists todos CASCADE 
Hibernate: drop sequence if exists hibernate_sequence
Hibernate: create sequence hibernate_sequence start with 1 increment by 1
Hibernate: create table todos (id bigint not null, completion_date date, description varchar(250) not null, priority varchar(250) not null, title varchar(50) not null, primary key (id))
Hibernate: alter table todos add constraint UK_c14g1nqfdaaixe1nyw25h3t0n unique (title)

I implemented controller, service and repositiory in Java within the Spring Boot application. I used Postman to test the implemented functionality and getting all Todos works well but creating a Todo fails for the first 4 times because of an

org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primarky key violated: "PRIMARY KEY ON PUBLIC.TODOS(ID) [1, 'Create xxx Todo', 'An xxx TODO must be created.', NULL, 'HIGH']"

This is the request body:

{
    "title": "Creating xxxx Todo via API",
    "description": "An xxxx TODO was created via API.",
    "id": null,
    "completionDate": null,
    "priority": "LOW"
}

This exception occurs 4 times with the following response:

{
    "timestamp": "2021-05-25T17:32:57.129+00:00",
    "status": 500,
    "error": "Internal Server Error",
    "message": "",
    "path": "/api/todo/create"
}

With the fifth attempt the Todo gets created:

{
    "title": "Create xxxx Todo via API",
    "description": "An xxxx TODO was created via API.",
    "id": 5,
    "completionDate": null,
    "priority": "LOW"
}

and the ID 5 was assigned to this record. Hence, the problem seems to be the number of inserted records during the H2 start-up when Spring Boot starts and initializes the H2 database.

In the Todo entity I annotated the id as follows:

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;

How can I solve this problem that when I try to access the creation endpoint of the Spring Boot application via postman?

Boogie

We(i) cannot see from your post, why there is a unique constraint on title, but there is one! (hibernate ...add constraint). But the (straight-forward) solution: Use unique titles!! (with every postman request) – Selfdevotion

You're very intent. After posting my question I already added the UNIQUE constraint (I edited this in my question yet) but the exception still occurs. The request I submitted using Postman contains a Todo with a title currently not existend in the database. – Boogie

..ok! (sry, the title thing confuses the case addionally) ..but now, we(i;) see: (if post is consistent): With AUTO (on h2), hibernate generates the hibernate_sequence (with start=1 and increment=1), uses that obviously ...and (of course) explains the first 4 failing ids (which are not considered from your init script)... solution(s)... – Selfdevotion

solution(s): 1. to tell hibernate to use the "correct auto incrementing strategy/sequence" 2. simple but hacky: to ALTER hibernate_seqence ..to 5. 3. (when you have an init script anyways): Omit @GeneratedValue (deactivate hiberante sequencing for that) – Selfdevotion

I'm facing the same but I don't understand your solution – Bombycid

Please have a look at #72403446 – Bombycid