Create AutoIncrement column/field in Apache Derby

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

Subscribe to get updates delivered to your inbox

2 Comments + Add Comment

  • 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!

  • 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 comment