Android Question rowid not being kept for the lifetime of the database

[simonwilliamson]

Running the code below seems to prove that the rowid on a table that has a row deleted is not keeping the rowid but re-assigning it again to the next inserted row. This has big implications for my database and is not how sqlite rowid works. Am I doing something wrong?

This is the log output for the code below
Insert 3 new rows
Loop through to read rowid and then delete that row
11
12
13
Now insert another 3 new rows
Read the list of rowid and it shows new rowids have not been added but given the ids of the 3 deleted rows:
11
12
13


Log("Insert 3 new rows")
For i = 0 To 2
Main.sql.ExecNonQuery($"INSERT INTO plots (siteID, userID) VALUES (${i},55)"$)
Next

Log("Loop through to read rowid and then delete that row")
Dim RS As ResultSet = Main.sql.ExecQuery("SELECT rowid, * FROM plots WHERE userID = 55")
Do While RS.NextRow
Log(RS.GetInt("rowid"))
Main.sql.ExecNonQuery($"DELETE FROM plots WHERE rowid = ${RS.GetInt("rowid")}"$)
Loop

Log("Now insert another 3 new rows")
For i = 0 To 2
Main.sql.ExecNonQuery($"INSERT INTO plots (siteID, userID) VALUES (${i},55)"$)
Next

Log("Read the list of rowid and it shows new rowids have not been added but given the ids of the 3 deleted rows:")
Dim RS As ResultSet = Main.sql.ExecQuery("SELECT rowid, * FROM plots WHERE userID = 55")
Do While RS.NextRow
Log(RS.GetInt("rowid"))
Loop

Regards,

Simon

 

[Mahares]

This has big implications for my database and is not how sqlite rowid works. Am I doing something wrong?

That is how SQLite works. If you want the rowid to constantly increase and not to be reused, you need to have a column in your table identified as INTEGER PRIMARY KEY AUTOINCREMENT. If that field is called userID, then that field becomes your rowid and it will not be reused. It is an alias for rowid, which means userID is the rowid and should be referred to by the name: userID. You did not show the structure of your table in your thread which could have helped. If the column is: INTEGER PRIMARY KEY it most likely keeps going up, but can inherit a value that was used by a deleted record rowid
Off topic, you really need to post your code inside code tags. Erel, constantly begs members to do so, Many times, it falls on deaf ears. It just makes the code easier to decipher. Also, you really need to construct all your queries as parameterized queries especially when you are inserting or deleting records.

 

[simonwilliamson]

Dear Mahares,

Thank you for taking the time to answer the question.
I had read that rowid was automatically an INTEGER PRIMARY KEY and lasted for the lifetime of the database but this was obviosity incorrect. I have added

rowid INTEGER PRIMARY KEY AUTOINCREMENT

to each table and this has now fixed the issue.

Off topic:
Apologies, I did not realise the code should be in tags. I will try to remember that for future posts.