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.0.0
Environment: Generic
Reporter: Satheesh Bandaram
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
