Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 93505 invoked from network); 10 Jul 2006 13:47:55 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 10 Jul 2006 13:47:55 -0000 Received: (qmail 68011 invoked by uid 500); 10 Jul 2006 13:47:53 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 67986 invoked by uid 500); 10 Jul 2006 13:47:53 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 67975 invoked by uid 99); 10 Jul 2006 13:47:53 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 10 Jul 2006 06:47:53 -0700 X-ASF-Spam-Status: No, hits=0.6 required=10.0 tests=NO_REAL_NAME,UPPERCASE_25_50 X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [65.195.181.55] (HELO dbrack01.segel.com) (65.195.181.55) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 10 Jul 2006 06:47:53 -0700 Received: from Desktop02 (desktop02.segel.com [65.195.181.45]) by dbrack01.segel.com (Postfix - We shoot spammers on site.) with ESMTP id 756C71D6BD for ; Mon, 10 Jul 2006 08:52:48 -0500 (CDT) Reply-To: From: Sender: "Michael Segel" To: "'Derby Discussion'" Subject: RE: Altering identity field error Date: Mon, 10 Jul 2006 08:51:46 -0500 Organization: MSCC MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 In-Reply-To: <5249383.post@talk.nabble.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 Thread-Index: AcakDcJrMskEchzdRYOK558mGzxaWAAGeNUA Message-Id: <20060710135248.756C71D6BD@dbrack01.segel.com> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N 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.