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

Attempt to modify an identity column error after resetting identity column

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.2.1.6
    • Fix Version/s: 10.2.2.0, 10.3.1.4
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239

      Description

      When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
      The following statements will reproduce the error.

      CREATE TABLE MYTABLE (
      id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
      ,col2 INT NOT NULL
      )

      – Insert using an explicit value on the ID-field
      INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)

      – Reset the identity field
      ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3

      – Try inserting another explicit value and the error below is thrown.
      INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)

      Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000

      – Although, this works fine
      INSERT INTO MYTABLE(COL2) VALUES(4)

        Attachments

        1. preserveColDefault_v1.diff
          9 kB
          Bryan Pendleton

          Issue Links

            Activity

              People

              • Assignee:
                bryanpendleton Bryan Pendleton
                Reporter:
                gramark Lars Gråmark
              • Votes:
                3 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: