HSQL identify auto increase ID

By mkyong | Updated: July 9, 2015

Viewed: 4,009 (+49 pv/w)

Tags: hsql identify

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', '[email protected]');
INSERT INTO users (name, email) VALUES ('alex', '[email protected]');
INSERT INTO users (name, email) VALUES ('joel', '[email protected]');

Output

0, mkyong, [email protected]
1, alex, [email protected]
2, joel, [email protected]

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, [email protected]
101, alex, [email protected]
102, joel, [email protected]

Reference

1. HSQLDB – Identity Auto-Increment Columns