CODEWITHSUNDEEP
javasqldatabasedb2hibernate-annotations
user2020677
user2020677

Reputation: 33

How can i refresh my primary key column?

Till recent time i was using hibernate @Entity annotation to map to database tables.All the primary keys are annotated with @GeneratedValue(strategy = GenerationType.IDENTITY)

I got a scenario where i need to create new schema + migrate data from old schema into new schema.(with few column changes like drop, length and type)

After successful migration of data to new schema tables when i try to insert data using Application its throwing an exception

[ERROR] util.JDBCExceptionReporter DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=1; _NewSchema_._TableName_ , DRIVER=3.51.90

I believe that application is trying to insert rows again with Primary key value starting from 1 because same application is working fine with empty tables.

I want data rows to be inserted with its primary key value as highest value of existing rows primary key .

Any help will be thank full :)

Upvotes: 2

Views: 1199

Answers (3)

Hunter Zhao
Hunter Zhao

Reputation: 4649

Your guess is correct, here is my solution, execute the following SQL to give the ID column a specified start position, then your application will work fine. 

alter table TABLE_NAME alter column ID set GENERATED BY DEFAULT RESTART WITH 10000;

Hope to help you :)

Upvotes: 1

Azfar Niaz
Azfar Niaz

Reputation: 1546

In case of generation type , IDENTITY, you should look for identity column to be auto incemental. @GeneratedValue(strategy = GenerationType.IDENTITY) required primary key column to be auto incremental.

Upvotes: 0

Rais Alam
Rais Alam

Reputation: 7016

Yes you can do that by altering the table. Alter the table and set starting index for identity column in DB2.

Suppose maximum rows for TBALE_A is 50 and name of identity column is TABLE_ID

ALTER TABLE TBALE_A ALTER COLUMN TABLE_ID
   RESTART WITH 51

Upvotes: 3

Related Questions