DB2 10.5 – When inserting, the database is trying to reuse index keys on GENERATE ALWAYS

db2indexprimary-key

I am trying to insert a row into a database table which has a GENERATED ALWAYS primary key column. The table has over 1000 rows, and the primary keys start at 1. When I try to do an insert, the error message indicates that DB2 is autogenerating the id 1 and trying to insert, which causes an error since that primary key already exists.

This is the error message:

One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because
the primary key, unique constraint or unique index identified by "1"
constrains table "PROJECTMGMT.TIMESHEET" from having duplicate
values for the index key.

This is my primary key:

"TIMESHEET_ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( 
   START WITH 1 INCREMENT BY 1 MINVALUE -2147483648 MAXVALUE 2147483647 
   NO CYCLE CACHE 20 NO ORDER 
),

To try to troubleshoot the issue, I changed the TIMESHEET_ID column to GENERATED BY DEFAULT so that I could manually enter a primary key value and the query worked.

I am fairly new to DB2, and this has me stumped. I haven't been able to find any fix for this.

Best Answer

Determine the largest current ID value:

select max(TIMESHEET_ID) from PROJECTMGMT.TIMESHEET

then reset the identity value:

alter table PROJECTMGMT.TIMESHEET 
  alter column TIMESHEET_ID restart with <whatever the max value is + 1>
Related Question