db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Saurabh Vyas (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1645) ALTER TABLE ... SET INCREMENT BY X... Turns off the "Generated By Default" identity column constraint
Date Thu, 19 Oct 2006 08:52:36 GMT
    [ 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

        

Mime
View raw message