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

Creating a table with an auto-increment primary key in an H2 database is straightforward. You'll typically use the SQL keywords `IDENTITY` or `AUTO_INCREMENT` along with the `PRIMARY KEY` constraint.

Here's how you can achieve this:

Using `IDENTITY` (Recommended):

The `IDENTITY` keyword is the standard way to define an auto-incrementing column in H2. It's often preferred due to its portability across different database systems.

Here's an example SQL statement:

CREATE TABLE ExampleTable (
  id INT IDENTITY PRIMARY KEY,
  name VARCHAR(255),
  value INT
);

In this SQL snippet:

- `id INT IDENTITY PRIMARY KEY` specifies that the `id` column is an integer, will auto-increment with each insertion, and also acts as the primary key for the table.

- `name VARCHAR(255)` indicates a column named 'name' that will contain text, up to 255 characters.

- `value INT` defines a column named 'value' that will store integer values.

Using `AUTO_INCREMENT` (Alternative):

H2 also supports `AUTO_INCREMENT`, which is another common syntax for defining an auto-increment column, often used in MySQL and some other databases.

Here's how the syntax looks:

CREATE TABLE ExampleTable (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  value INT
);

This statement is functionally equivalent to the previous example using `IDENTITY`. The `id` column will behave in the same way by incrementing automatically upon insertion of a new row.

Important Notes:

- The `PRIMARY KEY` constraint ensures that each value in the `id` column will be unique and not null.

- You don't need to specify values for the `id` column when inserting new records into the table; the database handles the auto-incrementing process automatically.

Example of Inserting Data:

Here’s how you might insert data into the table:

INSERT INTO ExampleTable (name, value) VALUES ('Item 1', 100);
INSERT INTO ExampleTable (name, value) VALUES ('Item 2', 200);

- The database will automatically assign unique values to the `id` field for each new record (e.g., 1 and 2, and so on).

Retrieving Data:

You can retrieve the data by using:

SELECT FROM ExampleTable;

This will show all records in the table, including their auto-generated `id` values.

By using either `IDENTITY` or `AUTO_INCREMENT`, you can set up a table with a primary key that automatically increments, making it suitable for tracking records in an H2 database.

Learn more about this