How to delete all the entries in the database and reinitialize it completely including the primary keys when reinstalling the app in Android, Room?

My question is: how can I delete all the entries in the database and restart the autogenerated primary keys from 0 when reinstalling an app in android studio? I am currently trying to get around with Room database for a personal project, and I already found out the Dao methods annotated with @Insert return a Long or an Array of Longs. That Long represents the primary key of the inserted entry as far as I know. I've not managed yet to make the get methods work, so I cannot see in the UI the elements of the table, but I inspect with simple printlns() the value returned by inserts, and they keep incrementing even after reinstalling and relaunching the app. I've also already read about

android:allowBackup="false"

property in the manifest file. So I set it to false as the post on stackoverflow suggested, but apparently to no use because the value returned by insert keeps increasing. So there might be 2 scenarios: either the records don't get deleted even though I set that property to false or they get deleted but the primary key is not reset, in which case I would still like to know how can I reset it.

Long value returned by insert

I added above a link including some searches I've made. I'm going to edit and add more if I find something else. Any advice would be highly appreciated.

When you use autoGenerate=True this does not in fact turn on generation of the value. Instead it introduces the AUTOINCREMENT constraint.

This constraint (rule) says that the generated value MUST be greater than any that have been used, whether or not the respective row still exists. It does this by storing the most recently generated value (the highest value) in a table sqlite_sequence which has a row per table and hence how it retains the highest/largest generated value.

One option would be to reset or remove the respective value in the sqlite_sequence table (it is not recommended to manipulate system tables).

Another option would be to not specify autoGenerate=true by either specifying autoGenerate=false or as false is the default omitting the code. However, Room handles the field differently.

Typically you would have:-

 @PrimaryKey(autoGenerate='true')
 val id: Long=0, ....

Room, when autoGenerate is true takes 0 to represent no specific value and thus the underlying insert code does not provide a value and thus the value is generated.

The same code, but when autoGenerate is false, the 0 is considered to be the value to be used and the value will not be generated.

If the code is changed to:-

@PrimaryKey
val id:Long=null, ....

Then by not specifying a value, then the null is passed to the insert and sqlite (in the special case of an INTEGER type column that is the primary key) generates the value.

Generation of the value with AUTOINCREMENT (autoGenerate=true) is based upon 1 + the higher of the highest value in the actual table (the id column) and the value stored for the table in sqlite_seqeunce (the highest ever allocated value).

Generation of the value without AUTOINCREMENT is simply 1 + the highest value in the actual table (which is 0 if there are no rows in the table).

• note these are simplified generation rules

However, the intent of such a column is to uniquely identify a row, using the value for other purposes such as expecting a monotonically increasing number (such as 1,2,3,4,5......) will likely lead to issues.

Demo

Consider the following two classes (based on the above):-

@Entity
data class AutoIncrementId(
    @PrimaryKey(autoGenerate = true)
    val id: Long=0
)
@Entity
data class GeneratedId(
    @PrimaryKey
    val id: Long?=null
)

Now the following functions (in an @Dao annotated interface named AllDAOs):-

@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(autoIncrementId: AutoIncrementId): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(generatedId: GeneratedId): Long

@Query("DELETE FROM autoincrementid")
fun emptyAutoGeneratedId(): Int
@Query("DELETE FROM generatedid")
fun emptyGeneratedId(): Int
@Query("UPDATE sqlite_sequence SET seq=0 /* WARNING RESETS FOR ALL TABLES */")
fun resetAutoGeneratedSequences(): Int

• Note the last is how you could reset

And then the following activity code:-

    db = TheDatabase.getInstance(this)
    dao = db.getAllDAOs()

    dao.insert(AutoIncrementId())
    dao.insert(GeneratedId())
    dao.insert(AutoIncrementId())
    dao.insert(GeneratedId())
    /* Set id as 100 */
    dao.insert(AutoIncrementId(100))
    dao.insert(GeneratedId(100))

    dao.emptyAutoGeneratedId()
    dao.emptyGeneratedId()
    dao.insert(AutoIncrementId())
    dao.insert(GeneratedId())

As can be seen all actions are undertaken in pairs (with and without autogenerate).

Initially 2 rows are inserted with the id's generated (the 2 rows would likely have id's 1 and 2).

Next two rows are inserted with the id specifically set to 100. Next ALL rows are deleted.

Finally a row is inserted into each resulting in:-

and :-

• both images via App Inspection

As can be seen the AutoIncrementId table (autoGenerate=true) has 101 as the id, even though all other rows were deleted prior to the row being inserted.

However, the GeneratedId table has an id of 1.

Note your question specifies 0 as the start. SQLite WILL NOT generated an id value of 0.

Resetting sqlite_sequence

If dao.resetAutoGeneratedSequences() is added after the inserts with an id of 100 and before the last two inserts then the result is that both id values are 1

You may wish to refer to https://www.sqlite.org/autoinc.html which expands upon the above. One thing to note is that AUTOINCREMENT is not recommended due to it's use incurring unnecessary inefficiencies in most cases.