Apache Derby Auto Increment Identity

  • Friday, June 6, 2014 7:38 PM EST
  • Last Edited: Sunday, June 15, 2014 4:23 PM EST

Problem: Creating an auto increment id in apache derby (java pre-installed database)
IS A HEADACHE!!! a lot of examples out there do not help you at all because
all of them are creating a table. i was looking for a syntax for alter column syntax but apache derby just won't cooperate!!!

Solution:
alter column sql syntax doesnt work for some reason. I tried and tried until of course I found the solution because if I hadn't I wouldn't be posting this blog. 

So all you have to do is:
1. Right click on your table you want to add an auto increment. 
2. Choose Grab Structure and it will ask you to save a .grab file. Save it to somewhere you can remember
*HINT: This Grab structure is very useful. you can recreate your tables in any machine*
3. Delete your table! Do not worry! i have done this a lot of times and works everytime.
ATTENTION!! : If there is data on your table though, you have to say good bye to them because they will be gone
4. Right click on tables folder and choose recreate table... 
5. Select the .grab file you made earlier
6. You will see a query CREATE TABLE [TABLE YOU JUST DELETED] .... 
7. click on edit table script and change your primary key column and add the following:
GENERATED BY DEFAULT AS IDENTITY 
So your primary key line should look like:
ID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY

and finally, you have a table with an auto increment. this way, you don't have to insert the ID to the table.

comments powered by Disqus