[
https://issues.apache.org/jira/browse/DERBY-7084?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17154097#comment-17154097
]
Richard N. Hillegas commented on DERBY-7084:
--------------------------------------------
This is the expected and documented behavior of an identity column whose value
is GENERATED BY DEFAULT. Please see
http://db.apache.org/derby/docs/10.15/ref/rrefsqlj37836.html It is also the
standard behavior according to my reading of the 2016 SQL Standard, part 2,
subclause 4.15.2 (Identity columns): the underlying sequence generator is
advanced only if the user does NOT supply a value for the column.
As Bryan said, the workaround is to SELECT the MAX value of the column after
the import finishes and then to advance the next value of the sequence
generator accordingly via an ALTER TABLE ALTER COLUMN ... RESTART WITH
statement.
> Identity column data import does not increment generator
> --------------------------------------------------------
>
> Key: DERBY-7084
> URL: https://issues.apache.org/jira/browse/DERBY-7084
> Project: Derby
> Issue Type: Bug
> Reporter: Makkus B.
> Priority: Major
>
> When importing table data containing identity column vales (defined as
> GENERATED BY DEFAULT AS IDENTITY) via SYSCS_IMPORT_DATA (or
> SYSCS_IMPORT_TABLE) the data will be imported (when identity column was
> defined as GENERATED BY DEFAULT AS IDENTITY) , but the (internal) table index
> generator will not be updated. If the column is further defined as UNIQUE (or
> PRIMARY KEY) new data might not be added to the table (if the internal index
> will match an imported one).
> The expected behaviour would be that the internal index would always stay
> ahead of identity values inserted to the table. This is: for a new table the
> index defaults to 1. If data is imported to a new table, the index should not
> stay at 1 but become 1+max(imported_index). So the table can be continued to
> be used after the import.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
