[
https://issues.apache.org/jira/browse/DERBY-6852?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15420147#comment-15420147
]
ASF subversion and git services commented on DERBY-6852:
--------------------------------------------------------
Commit 1756297 from [~bryanpendleton] in branch 'docs/trunk'
[ https://svn.apache.org/r1756297 ]
DERBY-6852: Allow identity columns to cycle
This patch was contributed by Danoja Dias (danojadias at gmail dot com)
This change adds documentation about the new CYCLE option for a
generated identity column. The pages that are modified are the reference
manual pages for CREATE TABLE, for the GENERATED column spec, and for
the CREATE SEQUENCE statement.
> Allow identity columns to cycle (as defined in SQL:2003)
> --------------------------------------------------------
>
> Key: DERBY-6852
> URL: https://issues.apache.org/jira/browse/DERBY-6852
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Peter Hansson
> Assignee: Danoja Dias
> Attachments: derby-6852_1.diff, derby6852doc.diff, derby_6852_2.diff,
> derby_6852_3.diff, derby_6852_4.diff, failTests.diff, script.sql
>
>
> Currently when an IDENTITY column reaches its maximum value it will produce
> an error.
> For tables that are used as 'transaction logs' or 'event logs' it often makes
> sense to let the table automatically start over with the first identity value
> again when the max is reached. This would be similar to the CYCLE option on
> Oracle's SEQUENCE and as defined in SQL:2003. And Derby is probably used
> quite often for this purpose, I guess, perhaps even more than other RDBMSs.
> At the moment every developer have to program their own logic for this.
> I propose to introduce the CYCLE option.
> The idea of CYCLE is based on the assumption that there's been a prior
> cleanup in the table rows so that it will be possible to re-use ids that have
> been used previously. If that is not the case - and a rollover happens - then
> a duplicate value error will occur. In this sense it can be argued that the
> CYCLE option will trade a _certain_ error for a _potential_ error. Most Derby
> users would possibly gladly accept such a bargain. In other words: This
> option will greatly enhance the usability of IDENTITY columns.
> The current implementation of IDENTITY columns SQL grammar in Derby is a
> subset of the SQL:2003 standard which is the first of the SQL standards to
> define IDENTITY columns. Interestingly the standard also defines the CYCLE
> option but this was never implemented in Derby. Also see [SQL-99 and SQL-2003
> features mapped to Derby|https://wiki.apache.org/db-derby/SQLvsDerbyFeatures]
> (scroll to T174).
> In other words: The proposal is simply to implement CYCLE as defined in
> SQL:2003.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
