[
https://issues.apache.org/jira/browse/DERBY-6852?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15390739#comment-15390739
]
Bryan Pendleton commented on DERBY-6852:
----------------------------------------
Actually, as I read the SQL 2003 section 11.62 more closely, I see that it
treats START WITH specially:
{code}
<common sequence generator options> ::= <common sequence generator option> ...
<common sequence generator option> ::=
<sequence generator start with option>
| <basic sequence generator option>
<basic sequence generator option> ::=
<sequence generator increment by option>
| <sequence generator maxvalue option>
| <sequence generator minvalue option>
| <sequence generator cycle option>
<sequence generator start with option> ::= START WITH <sequence generator start
value>
<sequence generator start value> ::= <signed numeric literal>
<sequence generator increment by option> ::= INCREMENT BY <sequence generator
increment>
<sequence generator increment> ::= <signed numeric literal>
<sequence generator maxvalue option> ::=
MAXVALUE <sequence generator max value>
| NO MAXVALUE
<sequence generator max value> ::= <signed numeric literal>
<sequence generator minvalue option> ::=
MINVALUE <sequence generator min value>
| NO MINVALUE
<sequence generator min value> ::= <signed numeric literal>
<sequence generator cycle option> ::=
CYCLE | NO CYCLE
{code}
So my question about the order of the options is rubbish: clearly the
standard says that if you specify START WITH, it has to be the **first**
option.
But I'm still confused about the comma between the various clauses,
and about whether or not we ought to be able to share the option
parsing between CREATE SEQUENCE and GENERATED ... AS IDENTITY.
> 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)
