Re: question about INTEGER PRIMARY KEY AUTOINCREMENT

On Wednesday, 19 February, 2020 21:24, ethan he <[email protected]> wrote:

>There is a SQLITE DATABASE has “MeslocallD”(INTEGER PRIMARY KEY
>AUTOINCREMENT),
>Is that possible to delete the data but still keep the MeslocallD
>consistence?

Assuming that by "consistence" you mean the high-water mark for inserted rowid then the answer is yes.  The high water mark rowid used for a table is stored, for each autoincrement rowid table, in a special table called sqlite_sequence.

If you delete all the data from the table, the sqlite_sequence will not be changed.  However, if you drop the table, the entry for that table will be removed from sqlite_sequence.  If you will be dropping the table and need to "remember" the high-water mark, you can select it from the sqlite_sequence table and re-insert it after re-creating the table.

sqlite> create table x(x integer primary key autoincrement);
sqlite> insert into x values (1);
sqlite> select * from sqlite_sequence;
x|1
sqlite> drop table x;
sqlite> select * from sqlite_sequence;
sqlite> create table x (x integer primary key autoincrement);
sqlite> select * from sqlite_sequence;
sqlite> insert into sqlite_sequence values ('x', 1);
sqlite> insert into x values (null);
sqlite> select * from x;
2
sqlite> select * from sqlite_sequence;
x|2

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users