db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lars Gramark <l...@stickybit.se>
Subject Altering identity field error
Date Mon, 10 Jul 2006 10:38:23 GMT

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.


Mime
View raw message