Re: [sqlite] Autoincrement with rollback

Hi Darren, Thanks for explaining the internals. I already assumed by my 2 small experiments that this was the case but it's nice to have it confirmed. With kind regards, Koen 2014-11-11 12:05 GMT+01:00 Darren Duncan : > On 2014-11-11 2:41 AM, Koen Van Exem wrote: >

Re: [sqlite] Autoincrement with rollback

On 2014-11-11 2:41 AM, Koen Van Exem wrote: I find it a bit confusing because when you create a PRIMARY KEY AUTOINCREMENT then a table named sqlite_sequence is created. According to the SQL (2003) standard multiple sessions are guaranteed to allocate distinct sequence values. (even when

Re: [sqlite] Autoincrement with rollback

I find it a bit confusing because when you create a PRIMARY KEY AUTOINCREMENT then a table named sqlite_sequence is created. According to the SQL (2003) standard multiple sessions are guaranteed to allocate distinct sequence values. (even when rollbacks are involved) 2014-11-11 11:14 GMT+01:00

Re: [sqlite] Autoincrement with rollback

delete it and commit it... rollback is 'undo' and if anything was differen't it wouldn't be a very good undo. assign the key yourself? on failure keep incrementing? if it's supposed to have been inserted and deleted... then rollback is not the correct solution. On Tue, Nov 11, 2014 at 2:04 AM,

Re: [sqlite] Autoincrement with rollback

> --- Begin --- > > sqlite> drop table if exists demo; > sqlite> create table demo (id integer primary key autoincrement, value > text); > sqlite> begin transaction; > sqlite> insert into demo (value) VALUES ('value'); > sqlite> select last_insert_rowid(); > 1 > sqlite> delete from demo where id

Re: [sqlite] Autoincrement with rollback

--- Begin --- sqlite> drop table if exists demo; sqlite> create table demo (id integer primary key autoincrement, value text); sqlite> begin transaction; sqlite> insert into demo (value) VALUES ('value'); sqlite> select last_insert_rowid(); 1 sqlite> delete from demo where id = 1; sqlite>

Re: [sqlite] Autoincrement with rollback

Koen Van Exem wrote: > Is it a bug or feature that the autoincrement > value is being reused when a rollback is issued? > > The documentation on https://www.sqlite.org/autoinc.html is a bit unclear > > ... it says it prevents reuse of ROWIDs from previously deleted rows. Only a DELETE statement

[sqlite] Autoincrement with rollback

Hi, Is it a bug or feature that the autoincrement value is being reused when a rollback is issued? --- Begin --- sqlite> drop table if exists demo; sqlite> create table demo (id integer primary key autoincrement, value text); sqlite> begin transaction; sqlite> insert into demo (value) VALUES