Attempt to modify an identity column error after resetting identity column

• Key: DERBY-1495
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Minor
• Assignee: Bryan Pendleton
• Reporter: Lars Gråmark
• Component/s: SQL
• Affects Version/s: 10.2.1.6
• Fix Version/s: 10.2.2.0, 10.3.1.4

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)