How to truncate a table?

Question

I need to truncate a table in my room database. Or I need to reset my autoincrement key to 0. Is there any way to do that.

score 3 · Answer 1 · answered Jan 14 '19 at 10:01

3

There is no TRUNCATE in Room(SQLite)

So execute DELETE FROM TABLE at first then execute UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME='TABLE_NAME'; to reset AI field.

i.e.

@Query("DELETE FROM TABLE_NAME")
    fun deleteTable(): Int

@Query("UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME='TABLE_NAME'")
    fun resetTable(): Int

answered Jan 14 '19 at 10:01

Rasel

5,488
3
30
39

1

try delete from sqlite_sequence where name='your_table'; after deleteTable() – Rasel Jan 14 '19 at 10:27

score 1 · Answer 2 · answered Jan 14 '19 at 09:53

1

https://developer.android.com/reference/android/arch/persistence/room/RoomDatabase#clearAllTables()

Note, this happens on the main thread and will delete all tables.

To clear one table you can add a @Query with DELETE FROM TableName; VACUUM

answered Jan 14 '19 at 09:53

Zun

1,553
3
15
26

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

As @Rasel mentioned , there is no TRUNCATE in Room. Just in brief, TRUNCATE TABLE removes the table data and Identifier column is reset to its seed value if the table contains an auto generate identity column. Unlike DELETE that just removes data, but retains the identity. Also there is another command named DROP that removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. but unfortunately there is no support for TRUNCATE and DROP in Room and it only know Delete command.

But there is a window of hope yet! According to sqlite documents, SQLite keeps track of the largest ROWID that a table has ever held using the special SQLITE_SEQUENCE table. The SQLITE_SEQUENCE table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. The content of the SQLITE_SEQUENCE table can be modified using ordinary UPDATE, INSERT, and DELETE statements.

A column with AUTOINCREMENT will be an alias of the rowid column which is used to uniquely identify a row. So if you can alter this table, you'll be able to reset autoincrement key! (As a point SQLite will set the first value to 1 not 0)

Another problem is that unexpected behavior Android Room not providing Access to other tables that was not created by classes, like SQLITE_SEQUENCE table. And as Room doesn't use SQLiteDatabase - but it uses SupportSQLiteDatabase, it delegates all calls to an implementation of SQLiteDatabase - you can't use SQLiteDatabase.execSQL() in this case.

So what I do in this cases, a function in Singletone RoomDatabase class (by Room + in Kotlin) is below:

        companion object {

        fun resetPointer(nonRoomDb: MyRoomDatabase){

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

    }       
}

Thanks to all this links:

How to truncate a table?

3 Answers3