Mc Gyver Basaya Mc Gyver Basaya - 4 months ago 15
MySQL Question

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?

Answer

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