SQLite - Is it possible to make a non-integer Primary Key work?

Question

Thanks for reading and hopefully answering,

Due to legacy issues and company policy, I am working with SQLite 2.8.17 and have a few varchars as primary keys.

But when I run a *"pragma integrity_check"*, it reports "rowid missing from index" and "wrong # of entries in index" on the tables with the varchar primary key but the returned result is an OK.

Extended Constant DB access and usage (about a day of load testing) seems to result in a DB failure (integrity check returns FAIL) and the only explanation (via .explain) is the same errors as earlier ("rowid missing from index" and "wrong # of entries in index").

Can anyone help? Is there something wrong that I am doing?

Thanks.

Answer 1

If you vacuum the database, that should at the very least re-construct it without this integrity check failure. Then, try a day of access and usage, as you say, and see if the error is still present.

To vacuum a database, either enter the database in command line and type: VACUUM;, or use a command line:

sqlite2.exe mydb.db "vacuum;"

(although I seem to remember that SQLite2 needed a table name after VACUUM, so you might need to experiment a little. It will affect all tables nonetheless.)