H2数据库IDENTITY列插入数据失败的技术求助

Hey Anand, let's break down why you're hitting this error and how to fix it quickly.

First, let's look at your table creation statement—there's a tiny syntax issue that's throwing H2 off: you have an extra comma at the end of your column definitions. That might seem trivial, but it can cause H2 to misinterpret the IDENTITY column's auto-increment behavior, treating it like a regular non-null column instead of one that generates values automatically.

Step 1: Correct Your DDL Statement #

Here's the fixed version of your table creation SQL, with the extra comma removed:

CREATE TABLE IF NOT EXISTS USER (
    USER_ID IDENTITY,
    USER_NAME VARCHAR(30) NOT NULL,
    USER_PASSWORD VARCHAR(256) NOT NULL
);

Step 2: Verify Auto-Increment Behavior (If Needed) #

H2's IDENTITY type is actually an alias for BIGINT AUTO_INCREMENT, so it should auto-generate values by default. But if you still run into issues after fixing the comma, you can explicitly define the column with AUTO_INCREMENT and set it as the primary key for clarity:

CREATE TABLE IF NOT EXISTS USER (
    USER_ID BIGINT AUTO_INCREMENT PRIMARY KEY,
    USER_NAME VARCHAR(30) NOT NULL,
    USER_PASSWORD VARCHAR(256) NOT NULL
);

Step 3: Run Your Insert Statement Again #

Once your table is created correctly, your original insert query will work perfectly—you don't need to provide a value for USER_ID because H2 will handle generating it for you:

INSERT INTO USER (USER_NAME, USER_PASSWORD) VALUES ('Anand', 'Anand$');

Why Did This Happen? #

That extra comma in your DDL confused H2's parser, preventing it from recognizing USER_ID as an auto-incrementing column. Without that auto-increment flag, H2 expects you to provide a value for the non-null column, hence the "NULL not allowed" error.

内容的提问来源于stack exchange，提问作者Anandhakrishnan