[
https://issues.apache.org/jira/browse/DERBY-6852?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Peter Hansson updated DERBY-6852:
---------------------------------
Description:
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.
was:
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 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.
There are possibly a number of ways to implement this in the SQL grammar.
Here's my suggestion:
{code:sql}
CREATE TABLE greetings
(greetings_id int GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1,
CYCLE),
ch char(3));
{code}
Very simple. Just like in Oracle SEQUENCEs the CYCLE option would make the
sequence start over from the current 'start with' value. Not specifying 'CYCLE'
implies 'NOCYCLE' which is the current behavior.
Currently - in this area - Derby conforms 100% to the SQL:2003 standard which
is where the IDENTITY syntax was introduced.The SQL:2003 standard doesn't
mention the possibility of a rollover/cycle option on the grammar which is
probably why each vendor does this on their own. My suggestion is at least a
superset of the SQL:2003 standard.
Summary: Allow identity columns to cycle (as defined in SQL:2003)
(was: Allow identity columns to cycle (similar to Oracle))
> 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
>
> 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)
