If you don't want to read the whole post then just do this:
Every time you create a table with sqlite make sure to have an INTEGER PRIMARY KEY AUTOINCREMENT column (the rowid column will be an alias to this one).
If you have some time then read on...
A lot of people don't realize that a rowid can change (I was part of the group until some time ago). As always a simple example worths more than 1000 words in my bad English :-)
You can test this example with
SQLiteManager.
Create a table without a primary key:
CREATE TABLE test (name TEXT);
Insert some data into the table:
INSERT INTO test (name) VALUES ('marco');
INSERT INTO test (name) VALUES ('giuly');
INSERT INTO test (name) VALUES ('gregory');
INSERT INTO test (name) VALUES ('house');
Perform the query SELECT rowid,* FROM test;
Here it is the result:
1 marco
2 giuly
3 gregory
4 house
Everything is fine until this point...
Now delete a couple of rows:
DELETE FROM test WHERE name='marco';
DELETE FROM test WHERE name='gregory';
Now perform again: SELECT rowid,* FROM test;
Here it is the result:
2 giuly
4 house
Everything is fine, no?
That's cool...
Now, perform a VACUUM on the database and run again the query:
SELECT rowid,* FROM test;
Here it is the result:
1 giuly
2 house
Rowids are changed!!!! So please take extra care when you define a table and need to reference records using rowids.
From the official documentation: "Rowids can change at any time and without notice. If you need to depend on your rowid, make it an INTEGER PRIMARY KEY, then it is guaranteed not to change". And I add also AUTOINCREMENT so you are sure that the same rowid(s) are not reused when rows are deleted.
Special thanks to Mike Bailey for pointed out this issue.
