Search code examples
javamysqlderby

create table user("user_id int auto_increment"); not working in derby (embedded database)

I am new in derby library. why I got this error when I use the auto_increment in my query?

here is my java code

 this.conn.createStatement().execute(create table user("user_id int auto_increment, PRIMARY KEY(user_id))");

I tried this in mysql server and its works but in derby I got this error

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "auto_increment" at line 1

why I got this error?

Solution

  • Derby does not have auto_increment as a keyword. In derby you need to use identity columns to implement auto increment behaviour

    For example

    CREATE TABLE students
(
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
name VARCHAR(24) NOT NULL,
address VARCHAR(1024),
CONSTRAINT primary_key PRIMARY KEY (id)
) ;

    Above statement will create Student table with id as auto increment column and primary key as well.

    Hope this helps