question about INTEGER PRIMARY KEY AUTOINCREMENT

[ethan he]

Hi,

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

Thanks for your help


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

[Hick Gunter]

The next value for an INTEGER PRIMARY KEY AUTOINCREMENT does not depend on the current contents of the table, only its history. While ROWIDs are monotnically increasing, there may be gaps in the sequence, caused by rows that failed to insert due to constraint violations. However, ROWIDs that get rolled back will be reused.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von ethan he
Gesendet: Donnerstag, 20. Februar 2020 05:24
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] question about INTEGER PRIMARY KEY AUTOINCREMENT

Hi,

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

Thanks for your help


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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) [phone] - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Keith Medcalf]

On Wednesday, 19 February, 2020 21:24, ethan he <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users