1

I have a large table without a primary key. Instead, I've been using the built-in ROWID as one.

Problem: now I can't VACUUM my database, because:

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

How can I convert the ROWID to an INTEGER PRIMARY KEY, preserving existing ROWIDs and their behavior (or otherwise get VACUUM to not mess up ROWIDs)?

Vladimir Panteleev
  • 24,651
  • 6
  • 70
  • 114
  • 1
    The only way I know of 'for certain' is to recreate the table(s) - but see http://stackoverflow.com/a/20561207/2864740 (hacks; YMMV). For the recreate approach SQLite has no problem with manually inserting the PK (or reading the OID) when copying the data. – user2864740 Jun 02 '15 at 20:18
  • @user2864740 The `writable_schema` hack will not work if the new table definition has even the slightest difference in its storage format. – CL. Jun 03 '15 at 08:26

0 Answers0