1

Android Room version 1.1.0 now provides the method clearAllTables().

Altough this method is very convenient, it does not clear the auto-increment value generated by the by autoGenerate(), as stated in the official documentation.

I would also like to clear the primary keys of all the tables in my database, without having to call individual queries for each table.

Tjaart
  • 496
  • 8
  • 20
  • 1
    see https://www.sqlite.org/autoinc.html, section `"The AUTOINCREMENT Keyword"` – pskink Jun 09 '18 at 18:06
  • Possible duplicate of [How do I completely recreate my database in Android Room?](https://stackoverflow.com/questions/44810358/how-do-i-completely-recreate-my-database-in-android-room) – Bink Oct 23 '18 at 16:45

1 Answers1

0

Based on this answer, I would assume that this SQL statement would work:

DELETE FROM sqlite_sequence WHERE name='table1' OR name='table2';

...chaining on as many OR clauses as needed.

However, sqlite_sequence is not a Room-managed table, so you may need to execute this SQL using a SupportSQLiteDatabase. You get one of those by calling getOpenHelper().getWritableDatabase() on your RoomDatabase.

However, I would not bother with this. IMHO, you should not be relying on any particular behavior of AUTOINCREMENT (e.g., always starting from some specific value). In that case, it's unclear what value there is in hacking SQLite to reset these values.

CommonsWare
  • 986,068
  • 189
  • 2,389
  • 2,491
  • It is not that I am relying on the Primary Key to start from some specific value, it is just that I am worried about any future problems should the primary key overflow. Do I have to worry about it overflowing? – Tjaart Jun 09 '18 at 18:23
  • @Tjaart: Um, well, things start to get interesting after the first 9,223,372,036,854,775,807 rows. The Sun might go nova before you use that many. See [the docs that pskink pointed to](https://www.sqlite.org/autoinc.html) for more details about the number allocation. – CommonsWare Jun 09 '18 at 18:29