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 ('value');
sqlite> select last_insert_rowid();
1
sqlite> rollback;
sqlite> begin transaction;
sqlite> insert into demo (value) VALUES ('value');
sqlite> select last_insert_rowid();
1
--- End ---
The documentation on https://www.sqlite.org/autoinc.html is a bit unclear
On an INSERT <https://www.sqlite.org/lang_insert.html>, if the ROWID or
INTEGER PRIMARY KEY column is not explicitly given a value, then it will be
filled automatically with an unused integer, usually the one more than the
largest ROWID currently in use. This is true regardless of whether or not
the AUTOINCREMENT keyword is used.
If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that
changes the automatic ROWID assignment algorithm to prevent the reuse of
ROWIDs over the lifetime of the database. In other words, the purpose of
AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted
rows.
In bullet 1 it says that it takes a value higher than the largest ROWID.
In bullet 2 it says it prevents reuse of ROWIDs from previously deleted
rows.
Can someone please clarify?
Thanks,
Koen
--
Koen Van Exem
+32 2335 2335
k...@allors.com
http://www.allors.com
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
