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

ALTER TABLE ... SET INCREMENT BY X... Turns off the "Generated By Default" identity column constraint

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.3.1
    • Fix Version/s: 10.2.2.0, 10.3.1.4
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Both Ubuntu Linux, Windows XP... Java 1.4.2_x and Java 1.5
    • Issue & fix info:
      Release Note Needed

      Description

      I have a table which has an auto-generated key:

      create table MyTable (
      TableId INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
      StringValue VARCHAR(20) not null,
      constraint PK_MyTable primary key (TableId)
      )

      I verify that GENERATED BY DEFAULT is set:

      SELECT * FROM
      sys.syscolumns col
      INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
      WHERE tab.tableName = 'MYTABLE' AND ColumnName = 'TABLEID'

      I'm pulling in data for which I need to preserve the ID's:

      INSERT INTO MYTABLE (TableId, StringValue) VALUES (1, 'test1')
      INSERT INTO MYTABLE (TableId, StringValue) VALUES (2, 'test2')
      INSERT INTO MYTABLE (TableId, StringValue) VALUES (3, 'test3')

      In the absense of the Derby 10.2 feature (ALTER TABLE WITH RESTART X), I try to just change the INCREMENT BY value and insert a row so that I can reset the "next" key value:

      ALTER TABLE MyTable ALTER TableId SET INCREMENT BY 50

      Then I insert a "dummy" record (which I will delete later...) to move the key upwards:

      INSERT INTO MYTABLE (StringValue) VALUES ('test53')

      However, I can now no longer insert explicit values into the primary key like this:

      INSERT INTO MYTABLE (TableId, StringValue) VALUES (-999, 'test3')

      I get this error: SQL Exception: Attempt to modify an identity column 'TABLEID'.

      Upon checking the sys.syscolumns table again, it verifies that the table no longer has an auto-generated key, but the TableId is still an identity column.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bryanpendleton Bryan Pendleton
                Reporter:
                alanbrito Alan Baldwin
              • Votes:
                1 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: