HSQL auto increase id

HSQL auto increment id

Most of the times, we insert a record and want the database to set the value of primary key or ID column rather than doing it ourselves.
The reason is, to set the primary key on our own, we need to know the next value and ensure that it is not already present in the database.

In this article, we will take a look at configuring HSQL primary key column to auto generate or auto increment its value.

Syntax
To enable HSQL, to automatically generate the value of primary key column, we need to use IDENTITY keyword while creating the table as shown below

CREATE TABLE LAPTOP (
ID INTEGER IDENTITY PRIMARY KEY,
MODEL VARCHAR(30) NOT NULL,
BATCHNUM VARCHAR(30) NOT NULL
);

Now, when inserting values in this table either by using an SQL query or using an ORM framework such as Hibernate, or from Spring data JPA etc, we do not need to provide the value of ID.
HSQLDB will automatically generate it.

Note that the first record will have an ID 0 and subsequent records will have it increased by 1, by default.
Set start value and interval
If you want that the ID should not start with 0 or should be incremented by n, then use the below syntax

CREATE TABLE LAPTOP ( 
ID INTEGER GENERATED BY DEFAULT 
AS IDENTITY(START WITH 1, INCREMENT BY 2) PRIMARY KEY, 
MODEL VARCHAR(30) NOT NULL, 
BATCHNUM VARCHAR(30) NOT NULL 
);

This will generate 1, 3, 5 as ID values.

This syntax will be valid from HSQL v1.7.2 as per the docs.

Hope the article was useful.