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

To create a table with an auto-increment primary key in the H2 database, you can use the `IDENTITY` keyword. This keyword will make the specified column an auto-incrementing primary key. Here's how you can do it:

1. Using `IDENTITY` keyword: The most common way is to use the `IDENTITY` attribute directly in the column definition. This tells H2 to automatically generate unique IDs for each new row inserted into the table.

2. Example SQL Statement: Here is an example of a SQL statement that shows how to create a table named `Products` with an auto-incrementing primary key:

CREATE TABLE Products (
  product_id INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(255),
  price DECIMAL(10, 2)
);

Note: `AUTO_INCREMENT` is an alias to `IDENTITY` so you can also create your table by using it instead of IDENTITY keyword. Here is the way to do it:

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

3. Explanation of the Code:

- `CREATE TABLE Products` : This command creates a new table named `Products`.

- `product_id INT PRIMARY KEY AUTO_INCREMENT` or `product_id INT PRIMARY KEY IDENTITY`: This defines the `product_id` column as an integer and marks it as the primary key. The `AUTO_INCREMENT` or `IDENTITY` keywords specify that this column's values will auto-increment with each new row.

- `product_name VARCHAR(255)`: This creates a column called `product_name` that holds strings, with a maximum of 255 characters.

- `price DECIMAL(10, 2)`: This sets up a `price` column for storing decimal values with a total of 10 digits, 2 of them after the decimal point.

4. Inserting Data: When you insert data into this table, you don't need to specify a value for the `product_id` column. H2 will automatically handle it.

INSERT INTO Products (product_name, price) VALUES ('Laptop', 1200.00), ('Mouse', 25.00), ('Keyboard', 75.00);

5. Retrieving Data: You can retrieve data from the table using a simple SELECT statement:

SELECT FROM Products;

By following these steps, you can successfully create a table with an auto-increment primary key in your H2 database, which will automatically generate a unique ID for each new row. This approach simplifies the process of managing unique identifiers and improves database efficiency.

Learn more about this