Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6852

Allow identity columns to cycle (as defined in SQL:2003)

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • None
    • 10.13.1.0
    • SQL
    • None
    • Newcomer, Release Note Needed

    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 (scroll to T174).

      In other words: The proposal is simply to implement CYCLE as defined in SQL:2003.

      Attachments

        1. derby_6852_2.diff
          9 kB
          Danoja Dias
        2. derby_6852_3.diff
          9 kB
          Danoja Dias
        3. derby_6852_4.diff
          22 kB
          Danoja Dias
        4. derby-6852_1.diff
          1 kB
          Danoja Dias
        5. derby6852doc.diff
          6 kB
          Danoja Dias
        6. failTests.diff
          26 kB
          Danoja Dias
        7. releaseNote.html
          3 kB
          Bryan Pendleton
        8. releaseNote.html
          3 kB
          Danoja Dias
        9. script.sql
          0.5 kB
          Bryan Pendleton
        10. z.sql
          8 kB
          Richard N. Hillegas

        Issue Links

          is depended upon by

          Improvement - An improvement or enhancement to an existing feature or task. DERBY-6870 Google Summer of Code 2016: Derby bug fixing

          • Major - Major loss of function.
          • Resolved
          is related to

          Bug - A problem which impairs or prevents the functions of the product. DERBY-6550 Bulk-insert causes identity columns to cycle when they shouldn't

          • Major - Major loss of function.
          • Resolved
          relates to

          Improvement - An improvement or enhancement to an existing feature or task. DERBY-6542 Improve the concurrency of identity columns by using SYS.SYSSEQUENCES

          • Major - Major loss of function.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. DERBY-6913 Document the new ability of identity columns to cycle

          • Major - Major loss of function.
          • Closed

          Activity

            People

              Dnj Danoja Dias
              phansson phansson
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: