HSQL identify auto increase ID

In HSQLDB you can use IDENTITY keyword to define an auto-increment column, normally, this is the primary key. Review below examples :

1. IDENTITY – Default

By default, the IDENTITY value is start with zero.

 
CREATE TABLE users (
  id INTEGER IDENTITY PRIMARY KEY,
  name VARCHAR(30),
  email  VARCHAR(50)
);

INSERT INTO users (name, email) VALUES ('mkyong', 'aaa@gmail.com');
INSERT INTO users (name, email) VALUES ('alex', 'bbb@gmail.com');
INSERT INTO users (name, email) VALUES ('joel', 'ccc@gmail.com');

Output

0, mkyong, aaa@gmail.com
1, alex, bbb@gmail.com
2, joel, ccc@gmail.com

2. IDENTITY – Start With

The IDENTITY value is starting with 100 and increase by 1.

CREATE TABLE users (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 100, INCREMENT BY 1) PRIMARY KEY,
  name VARCHAR(30),
  email  VARCHAR(50)
);

Output

100, mkyong, aaa@gmail.com
101, alex, bbb@gmail.com
102, joel, ccc@gmail.com

Reference

1. HSQLDB – Identity Auto-Increment Columns