[ http://issues.apache.org/jira/browse/DERBY-359?page=all ] Mamta A. Satoor closed DERBY-359: ---------------------------------
> GENERATED BY DEFAULT identity option should consume identity values even if > the transaction aborts. This would allow skipping over user inserted values > during system generation. > --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > Key: DERBY-359 > URL: http://issues.apache.org/jira/browse/DERBY-359 > Project: Derby > Type: Improvement > Components: SQL > Versions: 10.1.1.0 > Environment: Generic > Reporter: Satheesh Bandaram > Assignee: Mamta A. Satoor > > Using GENERATED BY DEFAULT identity column, user can specify a value to be > inserted into identity column. When the system generated value and this user > specified value match, if an unique index is present, an error is correctly > generated. However, it is desirable to skip this value for next identity > generation, so that next INSERT would pass. Currently, all subsequent insert > statements fail. > ij(CONNECTION0)> create table tauto(i int generated by default as identity, k > int); > 0 rows inserted/updated/deleted > ij(CONNECTION0)> create unique index tautoInd on tauto(i); > 0 rows inserted/updated/deleted > ij(CONNECTION0)> insert into tauto(k) values 1,2; > 2 rows inserted/updated/deleted > ij(CONNECTION0)> select * from tauto; > I |K > ----------------------- > 1 |1 > 2 |2 > 2 rows selected > ij(CONNECTION0)> insert into tauto values (4,4); > 1 row inserted/updated/deleted > ij(CONNECTION0)> insert into tauto(k) values 3; > 1 row inserted/updated/deleted > ij(CONNECTION0)> insert into tauto(k) values 4; <=== Expected > error. > ERROR 23505: The statement was aborted because it would have caused a > duplicate > key value in a unique or primary key constraint or unique index identified by > 'T > AUTOIND' defined on 'TAUTO'. > ij(CONNECTION0)> insert into tauto(k) values 5; <=== Would be > preferable to skip over identity value of '4' and use '5' > ERROR 23505: The statement was aborted because it would have caused a > duplicate > key value in a unique or primary key constraint or unique index identified by > 'T > AUTOIND' defined on 'TAUTO'. > ij(CONNECTION0)> insert into tauto(k) values 6; > ERROR 23505: The statement was aborted because it would have caused a > duplicate > key value in a unique or primary key constraint or unique index identified by > 'T > AUTOIND' defined on 'TAUTO'. > ij(CONNECTION0)> > At this point, all system generated identity value based inserts would > continue to fail. -- 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
