Create AutoIncrement column/field in Apache Derby

By | July 6, 2009

While creating a table a particular column / field can be made autoincrement as :

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)
) ;

The value of an autoincrement column increments automatically with every insert and doesnt need to be specified in the insert command. It is 1 + the value of the same field in the previous row.

Now data can be inserted as :

insert into students(name , address) values('Sanjay' , 'New Delhi');

Note that the value for the id field (which is an autoincrement field) is not specified. It fills automatically by taking the value of the previous row and adding 1 to it.

Last Updated On : 6th July 2009





4 thoughts on “Create AutoIncrement column/field in Apache Derby

  1. Mansur

    hy pls for that id are we going to declare it first and initialize it to zero, then later we shall increment it to id++ ?

  2. Eric

    Aha! Thank you so much for posting this; I’ve been failing my database’s CREATE TABLE queries all afternoon with SQL-y “AUTO_INCREMENT” / “IDENTITY” / “AUTOINCREMENT” / etc. primary key fields. Why would Derby insist on making such an awkward new auto-increment phrase? Nerve-wracking, but you have saved my evening with this quick explanation!

    Thank you!

  3. N

    I had a question.So how do you get that id that was generated???
    Need to then add data to a table that uses that PK as a FK.
    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *