mercredi 14 janvier 2015

sqlite repeats primary key autoincrement value after rollback

I expect that used and rollback(ed) PK values will be discarded by sqlite, but do not occur that way. here is the test:



PRAGMA foreign_keys = ON;
BEGIN TRANSACTION;
INSERT INTO _UIDlgt (id, TS) VALUES (null, '1421248181');
select max(id) from _UIDlgt; -- shows 6 ok, there were previous records
ROLLBACK;
select max(id) from _UIDlgt; -- shows 5 ok, was previous max value
INSERT INTO _UIDlgt (id, TS) VALUES (null, '1421248181');
select max(id) from _UIDlgt; -- shows 6 I expect 7 !!!!


This is the table creation:



CREATE TABLE _UIDlgt (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
TS INTEGER NOT NULL
);


Manual says that failed inserts skips PK values attempted to use, but noting about rollbacks.

The question is: Is there some setup to manage this behavior? or a trick?


Ms Sql Server works the way I expect. Sqlite version is 3.7.7.1 under PHP 5.4.11


Aucun commentaire:

Enregistrer un commentaire