What happens when you delete a record from a SQLite database table that has a primary key attribute which autoincrements? Do the rest of the records after the deleted record get shifted back one step? Or do you end up with a break in your table. Well as far as I’ve discovered, in SQLite3, you end up with breaks in your table. For instance, if you had data in your table like so:
record1, record2, record3, record4
(note: select count(*) from [table_name] gives 4)
and you delete record2, you end up with
record1, , record3, record4
(note: select count(*) from [table_name] still gives 4)
but that’s not reasonable because you didn’t want the record anymore and deleted it. I mean, that is why you deleted it, right? If you wanted a blank row you could have replaced the record with blank spaces. Inserting new data doesn’t even take advantage of the empty space, so if you keep deleting records and inserting new data, you can image what your table will look like.
That’s when you need to vacuum! Yup, vacuum. Oh and the nice thing is, you don’t have to do it yourself; you just have to issue the command
In SQLite, just execute the command “vacuum” after deleting a record and the table gets cleaned up nice. If you don’t want to keep doing this, before you create any tables in the database, set pragma auto_vacuum to 1 (on) and that will take care of it for you.?This also takes care of reordering your rowids. As a result,?doing a select * from [table_name] will now give us:
record1, record3, record4
(note: select count(*) from [table_name] gives 3, aah, much better).
This doesn’t seem to work when you have?an integer?primary key so you might have to do without it or use a different attribute (non-integer) as a primary key. It can save you a lot of frustration trying to figure out why your tables are acting weird.
more information here:
http://marc.info/?l=sqlite-users&m=118061508921524&w=2