db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "BALDWIN, ALAN J [AG-Contractor/1000]" <alan.j.bald...@monsanto.com>
Subject ALTER TABLE ... SET INCREMENT BY X... Turns off the "Generated By Default" identity column constraint
Date Fri, 04 Aug 2006 19:50:15 GMT
I have logged a JIRA issue for this:
https://issues.apache.org/jira/browse/DERBY-1645
<https://issues.apache.org/jira/browse/DERBY-1645> 


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') 

This has the desired effect of moving the IncrementValue, however, I can now
no longer insert explicit values into the primary key like this: 

INSERT INTO MYTABLE (TableId, StringValue) VALUES (-999, 'test3');
Generates this error: SQL Exception: Attempt to modify an identity column
'TABLEID'. 

Upon checking the sys.syscolumns table again, I verify that the table no
longer has an auto-generated key, but the TableId is still an identity
column, which is a problem, since we need to be able to insert explicit
values at times for this key.


Regards,
Alan Baldwin


---------------------------------------------------------------------------------------------------------
This e-mail message may contain privileged and/or confidential information, and is intended
to be received only by persons entitled to receive such information. If you have received
this e-mail in error, please notify the sender immediately. Please delete it and all attachments
from any servers, hard drives or any other media. Other use of this e-mail by you is strictly
prohibited.


All e-mails and attachments sent and received are subject to monitoring, reading and archival
by Monsanto. The recipient of this e-mail is solely responsible for checking for the presence
of "Viruses" or other "Malware". Monsanto accepts no liability for any damage caused by any
such code transmitted by or accompanying this e-mail or any attachment.
---------------------------------------------------------------------------------------------------------


Mime
View raw message