Why is the primary key not reset in android room?

Question

private fun savetosqlite(CoinListesi: List<CoinInfo>){ 

        launch{ 
            val dao = CoinInfoDatabase(getApplication()).CoinDao()
            dao.deleteAll()

            val uuidList= dao.insertAll(*CoinListesi.toTypedArray())
    
    }

dao is reset but primary key keeps increasing every time the function is called, also primary key doesn't start from 0 how do I solve it?

Dao

@Dao
interface CoinInfoDao {

@Insert
suspend fun insertAll(vararg CoinInfo: CoinInfo):List<Long> 

@Query("DELETE FROM CoinInfo")
suspend fun deleteAll() }

model

@Entity
 data class CoinInfo (...){
 @PrimaryKey(autoGenerate = true)
   var uuid:Int=0
}

score 0 · Accepted Answer · answered Jan 10 '23 at 23:26

Because autoGenerate/autoIncreament from the sqlite docs says it is to

prevent the reuse of ROWIDs over the lifetime of the database

As deleting all the rows with "DELETE FROM CoinInfo" does not affect the lifetime of this table in the database then the numbers continue to increase.

You need to end the "life" of the table with the SQL "DROP TABLE CoinInfo" and then re-create it for it to start a new lifetime and reset the auto generated Int.

Or you can directly reset the value of where SQLite stores then last number used with a query like "DELETE FROM sqlite_sequence WHERE name='CoinInfo'" (or set the value to be 0/1 of this row)

You would need to execute something like

CoinInfoDatabase.getDatabase(application)
.getOpenHelper()
.getWritableDatabase()
.execSQL("DELETE FROM sqlite_sequence WHERE name='CoinInfo'");

Or more efficient is do you really need autoincrement? as per the docs

Why is the primary key not reset in android room?

1 Answers1