title: Android Room - How to reset auto generated table primary key on each app run
body: <p>I'm using Room in order to persist data.<br />
  I have a Entity that has an automatically generated (autoGenerate) primary key that mimics a ticket system.
  <strong>On every application run I need this key to start from 0</strong>.</p>
  <p>Entity:</p>
  <pre><code>@Entity
  public class SequenceAction {
  
      @PrimaryKey(autoGenerate = true)
      private Integer sequenceId;
      private String actionType;
      private String extraInfo;
      //getters &amp; setters
  }
  </code></pre>
  <p>Initialization:</p>
  <pre><code>// init sequenceAction object
  // run with executor(sequenceId is automatically set on insert to table):
  AppDatabase.getInstance(getContext()).sequenceActionDao().save(sequenceAction);
  </code></pre>
  <hr />
  <p><strong>Things I've tried:</strong></p>
  <p>I use <code>AppDatabase.getInstance(getApplicationContext()).clearAllTables();</code>  to clear the tables on exit but this <strong>does not</strong> reset the key starting index, instead it starts where it left off on the last run.</p>
  <p>I have not found a way to do this using Room so I'm trying with a SimpleSQLiteQuery passed to a RawQuery method in my Dao:</p>
  <pre><code>//Dao
  @RawQuery()
  Integer init(SimpleSQLiteQuery query);
  
  //Passed query
  new SimpleSQLiteQuery(&quot;...query...&quot;);
  </code></pre>
  <p>I've tried the next queries:</p>
  <ol>
  <li><code>&quot;ALTER TABLE SequenceAction AUTO_INCREMENT = 0&quot;</code></li>
  </ol>
  <p>I get an error(I tried this with 'AUTOINCREMENT', same error):</p>
  <blockquote>
  <p>android.database.sqlite.SQLiteException: near &quot;AUTO_INCREMENT&quot;: syntax error (code 1): , while compiling: ALTER TABLE SequenceAction AUTO_INCREMENT = 0</p>
  </blockquote>
  <p>Probably because, as <a href="https://stackoverflow.com/questions/7594541/android-table-creation-failure-near-autoincrement-syntax-error">this question/answer</a> states, there is no autoincrement keyword in SQLite but rather a column declared INTEGER PRIMARY KEY will automatically autoincrement.</p>
  <ol start="2">
  <li><code>&quot;delete from sqlite_sequence where name='SequenceAction'&quot;</code></li>
  </ol>
  <p>No error but, the index is not reset either.</p>
  <ol start="3">
  <li><p>As suggested <a href="https://stackoverflow.com/questions/692856/set-start-value-for-autoincrement-in-sqlite">here</a>:</p>
  <p><code>&quot;UPDATE SQLITE_SEQUENCE SET seq = -1 WHERE name = 'SequenceAction'&quot;</code></p>
  </li>
  </ol>
  <p>No error but, no effect.</p>
  <ol start="4">
  <li><code>&quot;TRUNCATE TABLE 'SequenceAction';&quot;</code></li>
  </ol>
  <p>Error(Probably because <a href="https://stackoverflow.com/a/9599806/5064324">SQLite doesn't support the TRUNCATE command</a>):</p>
  <blockquote>
  <p>android.database.sqlite.SQLiteException: near &quot;TRUNCATE&quot;: syntax error (code 1): , while compiling: TRUNCATE TABLE 'SequenceAction';</p>
  </blockquote>
  <ol start="5">
  <li>So... last try: <code>DELETE FROM SequenceAction</code></li>
  </ol>
  <p>No error, no effect.</p>