db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-783) Enhance ALTER TABLE syntax to allow users to change the next value to be generated for an identity column
Date Mon, 23 Jan 2006 06:41:10 GMT
     [ http://issues.apache.org/jira/browse/DERBY-783?page=all ]

Mamta A. Satoor updated DERBY-783:
----------------------------------

    Attachment: Derby783AlterTableRestartWith012206.txt

Attaching a patch to put RESTART in non-reserved list method in the parser. Also, added couple
test cases to make sure RESTART can be used as object names in Derby.

> Enhance ALTER TABLE syntax to allow users to change the next value to be generated for
an identity column
> ---------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-783
>          URL: http://issues.apache.org/jira/browse/DERBY-783
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: Derby783AlterTableRestartWith010306.txt, Derby783AlterTableRestartWith011706.txt,
Derby783AlterTableRestartWith012206.txt
>
> Derby allows a user to change the interval between consecutive values of the identity
column using ALTER TABLE. But there is no way to change the next value to be generated for
an identity column. Such a support in Derby will be very handy for tables with identity column
defined as GENERATED BY DEFAULT and with a unique key defined on them. Column defined with
GENERATED BY DEFAULT allows system to generate values for them or allows the user to manually
supply the value for them. A column defined this way is very useful when the user might want
to import some data into the generated column manually. But this can create problems when
the system generated values conflict with manually inserted values.
> eg
> autocommit on;
> create table tauto(i int generated by default as identity, k int); 
> create unique index tautoInd on tauto(i); 
> insert into tauto(k) values 1,2;  -- let system generate values for the identity column
> -- now do few manual inserts into identity column
> insert into tauto values (3,3); 
> insert into tauto values (4,4); 
> insert into tauto values (5,5); 
> insert into tauto values (6,6); 
> insert into tauto values (7,7); 
> insert into tauto values (8,8); 
> -- notice that identity column at this point has used 1 through 8
> -- now if the user wants to let the system generate a value, system will generate 3 but
that is already used and hence
> -- insert will throw unique key failure exception. System has consumed 3 at this point.
> insert into tauto(k) values 9;
> -- the insert above will continue to fail with the unique key failure exceptions until
system has consumed all the values till 8
> -- If we add ALTER TABLE syntax to allow changing the next value to be generated, then
user can simply use that to change
> -- next value to be generated to 9 after the manual inserts above and then insert into
tauto(k) values 9 will not fail
> SQL standard syntax for changing the next generated value
> ALTER TABLE <tablename> ALTER <columnName> RESTART WITH integer-constant

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