[ http://issues.apache.org/jira/browse/DERBY-783?page=all ]
Mamta A. Satoor updated DERBY-783:
----------------------------------
Attachment: Derby783AlterTableRestartWith011706.txt
Attaching a new review package because few files have changed since the time
the last review package was generated. Can a commiter please commit this?
> 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
>
> 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
