title: In what cases should the AUTOINCREMENT be used instead of the default ROW ID?
body: <p>In Sqlite, there are two ways to create monotonically increasing primary key values generated by the database engine, through the default <code>ROWID</code> mechanism or through the <code>AUTOINCREMENT</code> mechanism.</p>
  <pre><code>sqlite&gt; -- Through the default ROWID mechanism
  sqlite&gt; CREATE TABLE foo(id INTEGER NOT NULL PRIMARY KEY, foo);
  sqlite&gt; INSERT INTO foo (foo) VALUES ('foo');
  sqlite&gt; INSERT INTO foo (foo) VALUES ('bar');
  sqlite&gt; SELECT * FROM foo;
  1|foo
  2|bar
  sqlite&gt;
  sqlite&gt; -- Through the AUTOINCREMENT mechanism
  sqlite&gt; CREATE TABLE bar(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, bar);
  sqlite&gt; INSERT INTO bar (bar) VALUES ('foo');
  sqlite&gt; INSERT INTO bar (bar) VALUES ('bar');
  sqlite&gt; SELECT * FROM bar;
  1|foo
  2|bar
  sqlite&gt; -- Only the AUTOINCREMENT mechanism uses the sqlite_sequence table
  sqlite&gt; SELECT * FROM sqlite_sequence WHERE name in ('foo', 'bar');
  bar|2
  </code></pre>
  <p>The <a href="https://www.sqlite.org/autoinc.html" rel="nofollow noreferrer">documentation</a> seems to suggest that using <code>AUTOINCREMENT</code> is bad:</p>
  <blockquote>
  <p>The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.</p>
  <p>If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.</p>
  <p>[With AUTOINCREMENT,] SQLite keeps track of the largest ROWID that a table has ever held using an internal table named &quot;sqlite_sequence&quot;.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. But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes.</p>
  </blockquote>
  <hr />
  <p><strong>In what cases is it appropriate to use the AUTOINCREMENT keyword?</strong></p>