db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: Altering identity field error
Date Mon, 10 Jul 2006 13:51:46 GMT
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:lars@stickybit.se]
> Sent: Monday, July 10, 2006 5:38 AM
> To: derby-user@db.apache.org
> 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.




Mime
View raw message