3

I created a table using Android's Room library, which stores items of type User. For the ID column, the value is automatically generated by adding the annotation

@PrimaryKey(autoGenerate = true)

In the next application run, I delete all entries in the table. However, the auto-generated value is not reset to 0, but continues where it left of the previous run.

How can I reset the auto-increment counter when I delete all entries in a table?

mjkdr
  • 66
  • 1
  • 5
  • 1
    Possible duplicate of [Android Room - reset auto generated key on each app run](https://stackoverflow.com/questions/50878734/android-room-reset-auto-generated-key-on-each-app-run) – lelloman Dec 16 '18 at 19:16
  • answered this recently: https://stackoverflow.com/a/53764393/549372 – Martin Zeitler Dec 16 '18 at 19:26
  • 1
    This may help you: https://stackoverflow.com/a/58616764/9726986 – Sampa Oct 30 '19 at 17:28
  • 1
    if your main concern is running out of integers for the row id then you shouldn't worry about that. sqlite will start using unused integers if the largest ROWID is equal to the largest possible integer (9223372036854775807) https://www.sqlite.org/autoinc.html – lasec0203 Apr 05 '20 at 07:03
  • @lasec0203 thanks just what i was looking for! – CyberShark Jun 08 '20 at 13:18

1 Answers1

1

Why would you, is perhaps the more pertinent question. A column with AUTOINCREMENT will be an alias of the rowid column which is used to uniquely identify a row. It isn't the best practice to rely upon this value other than for identifying a row.

Using AUTOINCREMENT (autogenerate = true) results in an internal table sqlite_sequence being created, a row in that table holds the value of the highest ever used rowid The next rowid will be that values + 1. Hence your issue.

As such to restart from 1 you would have to either update the respective row in the sqlite_sequence table to 0 or delete the respective row in the sqlite_sequence table.

You may wish to have a look at Android Room - reset auto generated key on each app run. Noting that this could be the basis for resetting the sequence value, but that it will always resets the sequence value.

MikeT
  • 51,415
  • 16
  • 49
  • 68
  • 2
    are you sure this still works with the current version of `Room`? recently I've spent a whole night with this, until I found out that `SupportSQLiteDatabase` rejects all `UPDATE` and `DELETE` queries on internal tables. – Martin Zeitler Dec 16 '18 at 19:37
  • That would depend upon what **this** is referring to. If it's the underlying principle of altering the respective row in **sqlite_sequence** then I don't believe that room uses anything other than the sqlite supplied with Android and due to historical versions would have to support this and not use some specially compiled version of SQLite that forces all tables to be WITHOUT rowid tables. – MikeT Dec 16 '18 at 19:43
  • also thought so... but it uses `SupportSQLiteDatabase`, which delegates to an implementation of `SQLiteDatabase`, instead of using the bare `SQLiteDatabase`... and that's the problem. only `SQLiteDatabase` will run queries which manipulate the internal tables. the answer linked in the comments above has an example project linked. – Martin Zeitler Dec 16 '18 at 19:50
  • No the issue is that sqlite_sequence stores the highest ever used rowid when AUTOINCREMENT (AUTOGENERATE = True) is used. Got nothing to do with SQliteDatabase v SupportSQliteDatabase. – MikeT Dec 16 '18 at 19:50
  • 1
    @MartinZeitler If by **this** (are you sure this still works with the current version of Room?) you are talking about the linked answer (which I believe is not the exact same issue, but is similar)? Then if you were to look at the code you will see that it uses **SQLiteDatabase** and does so BEFORE room opens the database, and as such opens the database using the **SQLiteDatabase open** method, and thus uses the **SQLiteDatabase execSQL** method. So I do believe that it still works. – MikeT Dec 17 '18 at 00:57