Tips on inserting manually assigning values into identity columns used in unique keys


Derby columns defined with GENERATED ... AS IDENTITY have an associated sequence generator that automatically assigns an incrementing value to the column when a record is inserted. Identity columns of type GENERATED BY DEFAULT (introduced in version 10.1) allow values to be manually inserted into identity columns and can result in duplicate values in the column. Problems can arise when applying a unique constraint to such a column (e.g. the column is used as a Primary Key). Insert failures will occur if the values manually inserted are later generated as part of the identity sequence.

Avoiding insert failures

If possible, avoid duplication of values by implementing a design where the key values are partitioned in a way that do not overlap. This can be most easily be accomplished by using identity column as part of a compound key where the values of the other key column differ for autogenerated and manually inserted values. If a single column key is required the key values from the various sources of data can be assigned ranges that will not overlap. For example, the records to be manually inserted from source A use the range of values from 1 to 1,000,000, the identity values generated range 1,000,001 to 2,000,000 the records manually inserted from source C range above 2 million. The starting value for the identity sequence as well as the value the sequence increments by can be specified when the column is created.

Addressing the problem

Currently (versions 10.0 and 10.1) if inserts fail because of duplicate values generated for the identity column the solution is to step through the conflicting values. The value generated for the sequence increments even when the insert fails so retrying the failed insert statement repeatedly will eventually generate a number that allows the insert to succeed.

Version 10.2 (estimated release: Q4 2006) will introduce the ability to restart an identity sequence using the ALTER TABLE command. This enhancement will allow a restart value (generally one larger than the largest value found in the column) to be specified so the sequence will jump beyond the conflicting values. The planned syntax is: {{{ ALTER TABLE <tableName>


UniqueIdentityAndInserts (last edited 2009-09-20 22:12:22 by localhost)