Uhm, Just a hunch... when you alter the identity column and reset the CBV (Current Base Value), do you also have to redefine GENERATE BY DEFAULT ?
It sounds that this could be the problem... > -----Original Message----- > From: Lars Gramark [mailto:[EMAIL PROTECTED] > Sent: Monday, July 10, 2006 5:38 AM > To: [email protected] > Subject: Altering identity field error > > > Hi, > > I'm trying out the new Derby alpha version 10.2.0.3-412239 and the new > identity reset features. > When I've altered an identity counter using the ALTER TABLE statement > below, > it seems as if the default identity behavior is lost. > Consider the following statements: > > 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) > > Is this a correct behavior? > I'd appreciate any thoughts on this. > > Best regards > Lars Gr?mark > -- > View this message in context: http://www.nabble.com/Altering-identity- > field-error-tf1917526.html#a5249383 > Sent from the Apache Derby Users forum at Nabble.com.
