db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Database identity column
Date Mon, 27 Nov 2006 10:59:50 GMT
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 restart 
>> the identity column. Have a look in the reference manual.
>> As an example:
>> ALTER TABLE myTable ALTER myIdentityColumn RESTART WITH 2000
>> HTH,
>> --
>> Kristian

View raw message