H2 Schema design

Key concepts and syntax for designing database schema for the H2 database

Auto-increment values in H2 database

The IDENTITY type is used for automatically generating an incrementing 64-bit long integer in H2 database.

CREATE TABLE public.account (
  id IDENTITY NOT NULL PRIMARY KEY ,
  name VARCHAR NOT NULL,
  number VARCHAR NOT NULL,
  sortcode VARCHAR NOT NULL,
  created TIMESTAMP WITH TIME ZONE NOT NULL);

The value for id is automatically generated by H2, so no need to provide a value for id in the SQL statement

INSERT INTO public.account ( id, name, number, sortcode, created)
VALUES ( ? , ? , ? , ? );

Resources

• next.jdbc documentation and next.jdbc db-types list
• H2 Database website
• SQL Constraints - W3Schools.com
• Purpose of constraint naming - Stack Overflow
• seancorfield/honeysql - SQL as data structures
• stack overflow - auto increment id in h2 database