db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Peter Hansson (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-6852) Allow identity columns to cycle (similar to Oracle)
Date Sat, 09 Jan 2016 10:01:39 GMT

     [ 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 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.



  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.




> Allow identity columns to cycle (similar to Oracle)
> ---------------------------------------------------
>
>                 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 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.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message