Android room - clearing sqlite_sequence is not working

Question

Following code is not resetting/clearing the auto increment id to 0

database = Room.databaseBuilder(getApplicationContext(), AppDatabase.class, DatabaseMeta.DB_NAME)
            .build();
database.query("DELETE FROM sqlite_sequence WHERE name = ?", new Object[]{"tableName"});

or

database.query("REPLACE INTO sqlite_sequence (name, seq) VALUES ('mission', -1)", null);

Is there anyway to clear sqlite_sequence in Android room?

Android room is not using "sqlite_sequence" for auto increment logic i think, not sure. Tried printing sqlite_sequence entries, can't find the table name i used(which has the auto increment primary key).

score 1 · Answer 1 · answered Mar 27 '18 at 12:32

The algorithm for determening the rowid when AUTOINCREMENT is used, and thus when sqlite_sequence is created/used, uses the higher of the highest existing rowid or the value (seq column) in the sqlite_sequence table.

As such if any rows exist in the associated table then setting the seq column will have no effect if it is not a value greater than the highest rowid in the associated table.

If the table has no rows then I believe that dropping the table will result in the respective row in the sqlite_sequence table being removed automatically.

score 1 · Accepted Answer · answered Feb 09 '19 at 10:49

Try this code works for me

class FeedReaderDbHelper extends SQLiteOpenHelper {
    static final int DATABASE_VERSION = 1;
    static final String DATABASE_NAME = DBConfig.DATABASE_NAME;

    FeedReaderDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public void onCreate(SQLiteDatabase db) {
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onCreate(db);
    }

    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onUpgrade(db, oldVersion, newVersion);
    }
}

FeedReaderDbHelper mDbHelper = new FeedReaderDbHelper(mainContext.getApplicationContext());
mDbHelper.getWritableDatabase().execSQL("DELETE FROM sqlite_sequence WHERE name='table_name';");

Seems Android room wont support on clearing sqlite_sequence, which is explained in https://stackoverflow.com/a/53764393/3442067. Have to create our own SQLiteOpenHelper to clear or modify sqlite_sequence table. — uday, Jan 20 '20 at 10:30

score 1 · Answer 3 · answered Jul 29 '20 at 11:52

I put this function in my RoomDatabase class:

        companion object {

    fun resetPointer(nonRoomDb: MyRoomDatabase){

      nonRoomDb.openHelper.writableDatabase.execSQL("DELETE FROM sqlite_sequence")

}       
}

So you can call it everywhere in code:

    val database = MyRoomDatabase.getInstance(application)

    MyRoomDatabase.resetPointer(database)

Android room - clearing sqlite_sequence is not working

3 Answers3