[ https://issues.apache.org/jira/browse/DERBY-6852?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15388739#comment-15388739 ]
Bryan Pendleton commented on DERBY-6852: ---------------------------------------- Is it possible that it's simply the location of the square brackets? That is, instead of {code} <START> <WITH> autoIncrementInitial = exactNumber() [<COMMA> <INCREMENT> <BY> autoIncrementIncrement = exactNumber() [<COMMA> <CYCLE> autoIncrementCycle = whetherCycle() ] ] {code} you could try: {code} <START> <WITH> autoIncrementInitial = exactNumber() [<COMMA> <INCREMENT> <BY> autoIncrementIncrement = exactNumber()] [<COMMA> <CYCLE> autoIncrementCycle = whetherCycle() ] {code} > 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, derby_6852_2.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)