You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Bryan Pendleton (JIRA)" <ji...@apache.org> on 2006/11/13 21:41:39 UTC
[jira] Resolved: (DERBY-1645) ALTER TABLE ... SET INCREMENT BY X...
Turns off the "Generated By Default" identity column constraint
[ http://issues.apache.org/jira/browse/DERBY-1645?page=all ]
Bryan Pendleton resolved DERBY-1645.
------------------------------------
Fix Version/s: 10.3.0.0
Resolution: Fixed
I've committed the patch attached to DERBY-1495 to subversion
as revision 474502. DERBY-1495 and DERBY-1645 are not
precisely speaking duplicates, but as was noted in the comments above,
a single fix resolves both problems. The patch changes the ALTER TABLE
handling so that the parts of the identify column that you aren't altering
are preserved and not incorrectly reset.
> ALTER TABLE ... SET INCREMENT BY X... Turns off the "Generated By Default" identity column constraint
> -----------------------------------------------------------------------------------------------------
>
> Key: DERBY-1645
> URL: http://issues.apache.org/jira/browse/DERBY-1645
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1
> Environment: Both Ubuntu Linux, Windows XP... Java 1.4.2_x and Java 1.5
> Reporter: Alan Baldwin
> Assigned To: Bryan Pendleton
> Fix For: 10.3.0.0
>
>
> I have a table which has an auto-generated key:
> create table MyTable (
> TableId INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
> StringValue VARCHAR(20) not null,
> constraint PK_MyTable primary key (TableId)
> )
> I verify that GENERATED BY DEFAULT is set:
> SELECT * FROM
> sys.syscolumns col
> INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
> WHERE tab.tableName = 'MYTABLE' AND ColumnName = 'TABLEID'
> I'm pulling in data for which I need to preserve the ID's:
> INSERT INTO MYTABLE (TableId, StringValue) VALUES (1, 'test1')
> INSERT INTO MYTABLE (TableId, StringValue) VALUES (2, 'test2')
> INSERT INTO MYTABLE (TableId, StringValue) VALUES (3, 'test3')
> In the absense of the Derby 10.2 feature (ALTER TABLE WITH RESTART X), I try to just change the INCREMENT BY value and insert a row so that I can reset the "next" key value:
> ALTER TABLE MyTable ALTER TableId SET INCREMENT BY 50
> Then I insert a "dummy" record (which I will delete later...) to move the key upwards:
> INSERT INTO MYTABLE (StringValue) VALUES ('test53')
> However, I can now no longer insert explicit values into the primary key like this:
> INSERT INTO MYTABLE (TableId, StringValue) VALUES (-999, 'test3')
> I get this error: SQL Exception: Attempt to modify an identity column 'TABLEID'.
> Upon checking the sys.syscolumns table again, it verifies that the table no longer has an auto-generated key, but the TableId is still an identity column.
--
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