db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "UniqueIdentityAndInserts" by StanleyBradbury
Date Mon, 14 Aug 2006 13:53:31 GMT
Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by StanleyBradbury:

New page:
## Added 2006-08-13
== Tips on inserting manually assigning values into identity columns used in unique keys 
=== Background ===
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> 
   ALTER COLUMN <identity ColName> 
   RESTART WITH <integer-constant>

View raw message