Hi,
That is how the identity columns work. Also if a failed transaction then
they will still increment. If the database is not closed correctly they
may jump by a large number. There is no guarantee that they will be in
increments of 1, just that they will be higher in value than the last.
If you need to guarantee that it increments by 1 you'll need to create
your own mechanism for doing that.
I guess in the case of the failed statement below, internally H2 it
still grabs the next value in the sequence to try the insert.
Hope that helps,
Ryan
On 22/05/2013 6:48 AM, Rustam Ismailov wrote:
Hi! I don't know is it a bug or feature, please explain.
Identity column is autoincremented, even previous statment completed
with an error (so I expect ID column value will not incremented).
Example snippet:
drop table TEST_TABLE if exists;
create table TEST_TABLE (
ID IDENTITY NOT NULL PRIMARY KEY, -- this column value we will observe
REQ_FIELD VARCHAR2(32) NOT NULL,
NOT_REQ_FIELD VARCHAR2(32)
);
insert into TEST_TABLE (NOT_REQ_FIELD) values ('foo'); -- this will
fail because of REQ_FIELD is still NULL - it is expected behavior, but
identity value incremented inside H2!
insert into TEST_TABLE (REQ_FIELD) values ('test'); -- this will be
executed fine, but ID value becomes 2 instead of (IMHO) expected 1,
see select statament below
COMMIT;
select * from TEST_TABLE; -- we see 2 in "ID" column, but expected 1
H2 Database version 1.3.171
java -version:
java version "1.7.0_21"
Java(TM) SE Runtime Environment (build 1.7.0_21-b11)
Java HotSpot(TM) 64-Bit Server VM (build 23.21-b01, mixed mode)
Windows 7 Ultimate x64 Russian.
Thanks!
--
You received this message because you are subscribed to the Google
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
