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 "Alan Baldwin (JIRA)" <de...@db.apache.org> on 2006/08/04 19:40:13 UTC
[jira] Created: (DERBY-1645) ALTER TABLE ... SET INCREMENT BY X...
Turns off the "Generated By Default" identity column constraint
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, 10.1.3.0
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
[jira] Commented: (DERBY-1645) ALTER TABLE ... SET INCREMENT BY
X... Turns off the "Generated By Default" identity column constraint
Posted by "Saurabh Vyas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1645?page=comments#action_12443455 ]
Saurabh Vyas commented on DERBY-1645:
-------------------------------------
I further investigated on this and following is the summary for that :
- when alter table is executed, ColumnDefinitionNode.validateDefault(DataDictionary dd, TableDescriptor td) get called.void
- - - - - - - - - - -
validateDefault(DataDictionary dd, TableDescriptor td)
throws StandardException
{
if (defaultNode == null ) // <--- See here
return;
//Examin whether default value is autoincrement.
if (isAutoincrement){
defaultInfo = createDefaultInfoOfAutoInc();
return;
}
........................
........................
- - - - - - - - - -
While the alter table statement does not support 'GENERATED BY' clause, the ModifiedColumnNode which gets created by the alter statement has defaultNode=null. Whereas for the original tree (before alter table), the defaultNode is not null and its value is available to ColumnDefinitionNode.validateDefault(DataDictionary dd, TableDescriptor td) in 'td' but is not used in the current code.
Thus leaving defaultInfo unset and hence throws exception in ResultColumnList.checkAutoincrement() while checking for cd.isAutoincAlways()
I tried to populate the defaultInfo for the alter table case as follows :
void validateDefault(DataDictionary dd, TableDescriptor td)
throws StandardException
{
//Check for defalutInfo from the exisiting TableData td
//and set defaultInfo
if (defaultNode == null ) {
ColumnDescriptorList cdl = td.getColumnDescriptorList();
ColumnDescriptor cd = cdl.getColumnDescriptor(td.getUUID(), this.getColumnName());
// Get the defaultInfo for the particular column from the exixiting values itself
// and set it for the modified column.
if (cd != null)
defaultInfo = (DefaultInfoImpl)cd.getDefaultInfo();
return;
}
//Examin whether default value is autoincrement.
if (isAutoincrement){
defaultInfo = createDefaultInfoOfAutoInc();
return;
}
................
................
This works fine for the following : (For the same table MYTABLE)
- ALTER TABLE MyTable ALTER TableId SET INCREMENT BY 50
- INSERT INTO MYTABLE (TableId, StringValue) VALUES (123, 'NewTest')
Well I could not provide a patch as derbyall was failing for lang/autoincrement.sql , it seems some where else it breaks the regression. I am not too clear with the sceniro. Correct me if I am wrong or if I am missing anything ??
Comments / Suggestions please.
- 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.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
[jira] Commented: (DERBY-1645) ALTER TABLE ... SET INCREMENT BY
X... Turns off the "Generated By Default" identity column constraint
Posted by "Kristian Waagan (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1645?page=comments#action_12450059 ]
Kristian Waagan commented on DERBY-1645:
----------------------------------------
Yes, since the patch is a fix for both issues, you should not see the bug with the head of the 10.2 branch anymore (or with trunk).
I assume the Fix Version field will be updated properly.
Note that to obtain the fix, you will have to build Derby yourself until a new release is out.
If you try it out, we would appreciate a confirmation that the patch fixes the problem you saw.
> 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, 10.2.1.8
>
>
> 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
[jira] Updated: (DERBY-1645) ALTER TABLE ... SET INCREMENT BY X...
Turns off the "Generated By Default" identity column constraint
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1645?page=all ]
Bryan Pendleton updated DERBY-1645:
-----------------------------------
Fix Version/s: 10.2.1.8
Thanks Alan for catching this. Yes, this fix is now in the 10.2 branch as well. Updated Fix Version to reflect this.
> 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, 10.2.1.8
>
>
> 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
[jira] Commented: (DERBY-1645) ALTER TABLE ... SET INCREMENT BY
X... Turns off the "Generated By Default" identity column constraint
Posted by "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
[jira] Resolved: (DERBY-1645) ALTER TABLE ... SET INCREMENT BY X...
Turns off the "Generated By Default" identity column constraint
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ 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
[jira] Commented: (DERBY-1645) ALTER TABLE ... SET INCREMENT BY
X... Turns off the "Generated By Default" identity column constraint
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1645?page=comments#action_12449066 ]
Bryan Pendleton commented on DERBY-1645:
----------------------------------------
I have attached a proposed patch to DERBY-1495 which also addresses this problem, I believe.
The proposed patch is slightly different from that suggested by Saurabh; I modified the
bindAndValidateDefault method in ModifyColumnNode to populate the defaultInfo as needed.
> 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
>
> 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
[jira] Commented: (DERBY-1645) ALTER TABLE ... SET INCREMENT BY
X... Turns off the "Generated By Default" identity column constraint
Posted by "Alan Baldwin (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1645?page=comments#action_12438204 ]
Alan Baldwin commented on DERBY-1645:
-------------------------------------
They are definitely closely related. Same symptom, different version of Derby. 1495 was logged against 10.1.3, 1645 was logged against 10.2. Derby 10.2 introduced the "ALTER TABLE WITH RESTART X" clause, and 10.1.3 did not have this. That is the only real difference.
Disclaimer: I'm not familiar with the code base, but I'm guessing that one fix may solve both issues.
> 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
[jira] Commented: (DERBY-1645) ALTER TABLE ... SET INCREMENT BY
X... Turns off the "Generated By Default" identity column constraint
Posted by "Alan Baldwin (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1645?page=comments#action_12450028 ]
Alan Baldwin commented on DERBY-1645:
-------------------------------------
Since DERBY-1495 was backported to version 10.2, can we assume that this issue was as well?
> 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
[jira] Commented: (DERBY-1645) ALTER TABLE ... SET INCREMENT BY
X... Turns off the "Generated By Default" identity column constraint
Posted by "Kristian Waagan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1645?page=comments#action_12443471 ]
Kristian Waagan commented on DERBY-1645:
----------------------------------------
Saurabh, your analysis seem to be identical to what
I reported in DERBY-1495. I also ran into trouble when trying to fix the problem.
In my case, Derby failed when clearing dependencies for the table when I dropped it.
> 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
>
> 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
[jira] Assigned: (DERBY-1645) ALTER TABLE ... SET INCREMENT BY X...
Turns off the "Generated By Default" identity column constraint
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1645?page=all ]
Bryan Pendleton reassigned DERBY-1645:
--------------------------------------
Assignee: Bryan Pendleton
> 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
>
> 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
[jira] Commented: (DERBY-1645) ALTER TABLE ... SET INCREMENT BY
X... Turns off the "Generated By Default" identity column constraint
Posted by "Bryan Pendleton (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1645?page=comments#action_12438133 ]
Bryan Pendleton commented on DERBY-1645:
----------------------------------------
Possible duplicate of DERBY-1495?
> 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