[DERBY-1495] Attempt to modify an identity column error after resetting identity column

• 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
• Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
• Assignee: Bryan Pendleton
• Reporter: Lars Gråmark
• Created: 11/Jul/06 04:50
• Updated: 13/Dec/07 09:05
• Resolved: 13/Nov/06 20:38

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)

Issue Links

• is duplicated by: DERBY-1645 ALTER TABLE ... SET INCREMENT BY X... Turns off the "Generated By Default" identity column constraint