SQLite3 and Implicit rowid's

Question

SQLite3 gives you a default primary key called rowid for each table if you don't specify a primary key. However, it looks like there are some disadvantages to relying on this:

The VACUUM command may change the ROWIDs of entries in tables that do not have an explicit INTEGER PRIMARY KEY.

http://www.sqlite.org/lang_vacuum.html

I want to alter an existing SQLite3 database to use explicit primary keys rather than implicit rowid's so I have the ability to run vacuum when necessary. Can I do this without rebuilding the whole database?

Answer 1

You don't need to rebuild the whole database. However since SQLite doesn't support ALTER TABLE statements you need to:

1. create a temporary table with the correct schema
2. copy all data from the original table to the temp table
3. delete the original table
4. rename the temp table

I suggest you use a app such as SQLiteman to do this for you.