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

To create a table with an auto-incrementing unique primary key in an H2 database, you can utilize the IDENTITY keyword during table creation. This tells the database to automatically generate a unique and sequential number for the primary key column each time a new row is inserted.

Here's how you can define a table with an auto-incrementing primary key using SQL:

1. Using the `IDENTITY` keyword

The IDENTITY property, similar to AUTO_INCREMENT in other databases, is used to generate auto-incrementing values. Here's an example:

CREATE TABLE Users (
   id INT PRIMARY KEY AUTO_INCREMENT,
   username VARCHAR(255) NOT NULL,
   email VARCHAR(255) NOT NULL
);

In this example, the id column is set as the primary key, and the IDENTITY property ensures that it auto-increments with each new record. The primary key constraint ensures uniqueness.

2. Considerations

- The data type for the primary key is commonly INT (Integer), but you can use BIGINT for larger tables.

- If you don't explicitly specify AUTO_INCREMENT, H2 will still increment the IDENTITY column when a new record is inserted without a specified value for the column.

- H2 does not have a separate "AUTO_INCREMENT" keyword. The term "IDENTITY" covers the functionality of AUTO_INCREMENT from other SQL databases.

3. Example with full table creation script:

CREATE TABLE Products (
   product_id INT PRIMARY KEY IDENTITY,
   product_name VARCHAR(255) NOT NULL,
   price DECIMAL(10, 2) NOT NULL,
   description VARCHAR(1000)
);

In this case, product_id will be the primary key and will automatically increment each time a new record is added to the Products table.

By using IDENTITY you can easily manage auto-incrementing IDs which is a common practice for primary keys in most relational databases, including H2.

Learn more about this