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

Question

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.

Answer 1

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>

Answer 2

GENERATE BY DEFAULT means that you can provide a value or you can allow DB2 to provide a value. If you use GENERATE BY DEFAULT and you provide a value, you may need to ALTER TABLE to reset the identity as DB2 will retain the last value it was at (rather than what you inserted into the table).

GENERATE ALWAYS means just that. Only DB2 can create values for this field. You cannot INSERT a value into this field. Just remove the identity value from your INSERT statements and you will be fine. Now if you use IMPORT or db2move to move the data (I'm thinking LOAD too, but not positive on that), this is a constraint, so you may need to drop the constraint first, bring in the data, and then add the constraint back and ALTER TABLE to reset where the identity starts with.

• Wige: As mentioned, the table was originally GENERATE ALWAYS, and there was no value specified for the primary key field. I just changed it and added the primary key to make sure that was in fact the issue.