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.

About Silver Moon

A Tech Enthusiast, Blogger, Linux Fan and a Software Developer. Writes about Computer hardware, Linux and Open Source software and coding in Python, Php and Javascript. He can be reached at [email protected].

5 Comments

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 *