db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "BALDWIN, ALAN J [AG-Contractor/1000]" <alan.j.bald...@monsanto.com>
Subject RE: Database identity column
Date Mon, 27 Nov 2006 23:10:59 GMT
I'm not sure about that last part, but be aware that JIRA 1495
[http://issues.apache.org/jira/browse/DERBY-1495] was only fixed in
version  If you have a version previous to that, you will see
unexpected results (your column will no longer be auto-generated) if you
use the WITH RESTART clause.


-----Original Message-----
From: Kristian Waagan [mailto:Kristian.Waagan@Sun.COM] 
Sent: Monday, November 27, 2006 5:00 AM
To: Derby Discussion
Subject: Re: Database identity column

Jatin Trivedi wrote:
> Thanks Kristian
> I need to nest the query  
> ALTER TABLE myTable ALTER myIdentityColumn RESTART WITH 2000 
> like 
> ALTER TABLE myTable ALTER myIdentityColumn RESTART WITH (select
> max(myIdentityColumn)  from myTable +1  )
> How can i do it ?

I don't know if you can. Anyone else?

My gut feeling would be that you need to alter the way your data is 
loaded, but I have no information about how that is done.
Could you give us some more information?
For instance:
   * Do you have a SQL script that you execute to load the data?
   * Are you exporting/importing data programatically?
   * Are you using a 3rd party application to migrate the data?
   * Also, how are the values of the identity column represented?
     (DEFAULT, value, constant?)
   * Are you using ij?

Based on your second question, I believe you want to automate the 
resetting of the identity column. If it is a one-time task, you can of 
course just use ij, do the select max and insert the value into the 


> Kristian Waagan wrote:
>> jatintrivedi wrote:
>>> I have exported a sybase db and mapped it to Derby. 
>>> Derby creates the corresponding data and schema xml. 
>>> One of the table has 20 rows and an auto-incremet primary key.
>>> When i migrate my application to derby and try to insert new row it
>>> starts
>>> with primary key id as 1 
>>> which causes an exception (unique constraint).
>>> How can i maintain the primary key after migrating to derby 
>> Hi,
>> What version of Derby are you using?
>> If you're using 10.2, you can use the ALTER TABLE statement to
>> the identity column. Have a look in the reference manual.
>> As an example:
>> ALTER TABLE myTable ALTER myIdentityColumn RESTART WITH 2000
>> HTH,
>> --
>> Kristian

This e-mail message may contain privileged and/or confidential information, and is intended
to be received only by persons entitled to receive such information. If you have received
this e-mail in error, please notify the sender immediately. Please delete it and all attachments
from any servers, hard drives or any other media. Other use of this e-mail by you is strictly

All e-mails and attachments sent and received are subject to monitoring, reading and archival
by Monsanto. The recipient of this e-mail is solely responsible for checking for the presence
of "Viruses" or other "Malware". Monsanto accepts no liability for any damage caused by any
such code transmitted by or accompanying this e-mail or any attachment.

View raw message