[
https://issues.apache.org/jira/browse/DERBY-6852?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15418167#comment-15418167
]
Bryan Pendleton commented on DERBY-6852:
----------------------------------------
I think that the ALTER TABLE issue can be handled in
AlterTableConstantAction.java. If you go to about line 2217
and look for the comment referencing DERBY-6579, you will see
that ALTER TABLE does some special work when we are just changing
the INCREMENT BY to make sure that it preserves the current value.
We could, potentially, do something similar.
HOWEVER, I think we should treat that as a separate, independent
project, something to be done in the future.
That is, I think we should file 3 new JIRA issues to track follow-on
work that we can do at some future time:
1) Change ALTER TABLE so that it doesn't reset CYCLE to 0 when you
alter the INCREMENT BY or RESTART WITH values.
2) Create a new ALTER TABLE variation that allows a user to alter a
generated identity column to turn the CYCLE option either on or off
3) Enhance sys.syscolumns to report the CYCLE value for an identity
column
Arguably the first problem is a bug, while the second two are
improvements, but either way I don't want to get bogged down with
those issues causing "feature creep" in the current effort.
I still think it would be great if the current patch could include
test cases that demonstrate the existing behavior in all of these areas:
1) test cases demonstrating the current ALTER TABLE behavior
2) test cases demonstrating the current values of sys.syscolumns and
sys.syssequences when defining identity columsn both with and without the CYCLE
option.
Unless anyone feels otherwise, I'll create the new JIRA issues in the next few
days.
> 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, 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)
