Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <11847236.1152568289866.JavaMail.jira@brutus>
Date: Mon, 10 Jul 2006 21:51:29 +0000 (GMT+00:00)
From: =?utf-8?Q?Lars_Gr=C3=A5mark_=28JIRA=29?= <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Created: (DERBY-1495) Attempt to modify an identity column
 error after resetting identity column
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Attempt to modify an identity column error after resetting identity column
--------------------------------------------------------------------------

         Key: DERBY-1495
         URL: http://issues.apache.org/jira/browse/DERBY-1495
     Project: Derby
        Type: Bug

  Components: SQL =20
    Versions: 10.2.0.0   =20
 Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.=
3-412239
    Reporter: Lars Gr=C3=A5mark
    Priority: Minor


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 Stat=
e: 42Z23, Error Code: 30000

-- Although, this works fine
INSERT INTO MYTABLE(COL2) VALUES(4)

--=20
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira