[jira] Commented: (DERBY-1645) ALTER TABLE ... SET INCREMENT BY X... Turns off the "Generated By Default" identity column constraint

["Saurabh Vyas (JIRA)" <de...@db.apache.org>]

    [ http://issues.apache.org/jira/browse/DERBY-1645?page=comments#action_12438080 ] 
            
Saurabh Vyas commented on DERBY-1645:
-------------------------------------

I have investigated on this issue and found out that when we create a table with 'GENERATED BY DEFAULT AS IDENTITY' , the 'columnDefaultInfo' attribute for ColumnDescriptor gets set to GENERATED_BY_DEFAULT. When we alter the table and set a new increment  by:

 - ALTER TABLE MyTable ALTER TableId SET INCREMENT BY 50
we override the value for 'columnDefaultInfo' and set it to 'null'. as 'GENERATED BY DEFAULT....' clause is not allowed here. Now when we try to insert a row with specifying value of  'TableId' as :

 - INSERT INTO MYTABLE (TableId, StringValue) VALUES (-999, 'test3')
the ResultColumnList.checkAutoincrement() throws an exception

if ((sourceRC != null) &&
    (sourceRC.isAutoincrementGenerated()))
    {
          sourceRC.setColumnDescriptor(cd.getTableDescriptor(), cd);
    }else{
          if(cd.isAutoincAlways())   // <-----   SEE HERE !! 
              throw StandardException.newException(SQLState.LANG_AI_CANNOT_MODIFY_AI,
                  rc.getName());
    }

Here, isAutoincAlways() is called for the ColumnDescriptor 'cd' for TableId :

public boolean isAutoincAlways(){
        return (columnDefaultInfo == null) && isAutoincrement();
    }

it returns true as  'columnDefaultInfo' is now 'null' and hence the exception.

For this case to work correctly, ALTER TABLE should change the INCREMENT BY clause without setting the 'columnDefaultInfo' to 'null'. Correct me if I am wrong or if I am missing anything ??

Comments / Suggestions please.

Thanks in advance,
Saurabh


> 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.0, 10.1.3.1
>         Environment: Both Ubuntu Linux, Windows XP... Java 1.4.2_x and Java 1.5
>            Reporter: Alan Baldwin
>
> 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