How do I create a table with an auto-increment unique primary key in H2 database?

To create a table with an auto-increment unique primary key in an H2 database, you can use the IDENTITY keyword along with the PRIMARY KEY constraint during table creation. Here's how:

1. Using the IDENTITY Keyword:

- The IDENTITY keyword in H2 signifies that the column will act as an auto-incrementing value. You can specify the starting value and the increment step (though the defaults are usually sufficient).

2. Setting as Primary Key:

- To ensure that the column is unique and acts as a primary key, you must use the PRIMARY KEY constraint.

3. Example SQL Script:

Here's the SQL code to create a table named my_table with an auto-increment primary key column named id:

CREATE TABLE my_table (
  id INT IDENTITY PRIMARY KEY,
  name VARCHAR(255),
  description VARCHAR(1000)
);

Explanation of the code:

- CREATE TABLE my_table: This statement initiates the creation of a new table named "my_table".
- id INT IDENTITY PRIMARY KEY: This part defines the column id. INT specifies it will hold integer values. IDENTITY designates this column for auto-incrementation. PRIMARY KEY ensures every entry will be unique and will work as a primary key.
- name VARCHAR(255): Creates a column to store strings of up to 255 characters, called name.
- description VARCHAR(1000): Creates a column to store strings of up to 1000 characters, called description.

4. Alternative with Explicit Start and Increment:

If you want to specify a starting value and increment step, you can modify the IDENTITY as follows:

CREATE TABLE my_table (
  id INT IDENTITY(100, 5) PRIMARY KEY,
  name VARCHAR(255),
  description VARCHAR(1000)
);

In this case, the ID starts at 100, and each new entry will increment by 5 (100, 105, 110...).

5. Using it in Code:

When you insert data into the table you don't have to specify a value for the id column, it will be automatically generated:

INSERT INTO my_table (name, description) VALUES ('item 1', 'first item');
INSERT INTO my_table (name, description) VALUES ('item 2', 'second item');

By using the IDENTITY keyword along with PRIMARY KEY in your H2 database table creation, you can create an auto-increment unique primary key effectively.

Learn more about this