android sqlite3 table vacuum not working

Question

I need to reorder the rowid sequentially after deleting some rows. Vacuum on table seems to be a good fit for the situation but for some reasons its not reordering the rowid.

sqlite> .schema inboxmessages 
CREATE TABLE InboxMessages(id text not null, title text not null, text text not null, senders_username text not null, created_at text not null, sender_image text not null, read text not null, Unique(id));

sqlite> select rowid, id from inboxmessages limit 5;
1|746915
3|746540
4|746195
5|745403
6|745371
sqlite> vacuum inboxmessages;
sqlite> select rowid, id from inboxmessages;
1|746915
3|746540
4|746195
5|745403
6|745371

whats wrong ?

score 1 · Accepted Answer · answered Nov 26 '14 at 09:10

VACUUM is not guaranteed to reorder rowid values, and if there is some INTEGER PRIMARY KEY column, it never will.

If you want to have specific values for your rowids, you have to set them manually.

Please note that in most cases, it is a better idea to just leave gaps in the rowid sequence.

android sqlite3 table vacuum not working

1 Answers1