SQLite starting rowid of 0
<p>I'm trying to set up a SQLite table where the rowid starts from 0 instead of the default 1. The end goal is to be able to run the first INSERT statement and have it insert to rowid 0. Explicitly setting rowid to 0 for that first INSERT is not an option.</p>

<p>I've tried a few things related to AUTOINCREMENT but am not having any luck getting this to work cleanly. The only successful way I've found is to insert a row with rowid of -1 and then delete it later. This works but it's messy and I'd like to find a cleaner way of doing it. I am working in Python 2.7 with the built-in sqlite3 library.</p>

<p>The bottom-line question:</p>

<p>Is there a cleaner way to start rowid from 0 other than manually inserting a -1 value and then removing it later?</p>

<hr>

<p>Some side information:</p>

<p>I found a similar question here and played with some AUTOINCREMENT settings: <a href="https://stackoverflow.com/questions/692856/set-start-value-for-autoincrement-in-sqlite">Set start value for AUTOINCREMENT in SQLite</a></p>

<p>The sqlite_sequence table doesn't seem to work with negative numbers. I used the following to test it:</p>

<pre><code>import sqlite3
con = sqlite3.Connection('db.db')
cur = con.cursor()

cur.execute("CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT)")
cur.execute("INSERT INTO sqlite_sequence (name,seq) VALUES (?,?)", ('test',-1))
cur.execute("INSERT INTO test (val) VALUES (?)", ('testval',)) #becomes rowid 1
cur.execute("INSERT INTO test (val) VALUES (?)", ('testval',)) #becomes rowid 2
cur.execute("INSERT INTO test (val) VALUES (?)", ('testval',)) #becomes rowid 3

cur.execute("SELECT rowid, id, val FROM test")
print cur.fetchall()
</code></pre>

<p>With the -1 inserted into sqlite_sequence it should set the next rowid to 0, but it's using 1 instead. If sqlite_sequence is initialized to a positive number the rowids are as expected.</p>

<pre><code>import sqlite3
con = sqlite3.Connection('db.db')
cur = con.cursor()

cur.execute("CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT)")
cur.execute("INSERT INTO sqlite_sequence (name,seq) VALUES (?,?)", ('test',10))
cur.execute("INSERT INTO test (val) VALUES (?)", ('testval',)) #becomes rowid 11
cur.execute("INSERT INTO test (val) VALUES (?)", ('testval',)) #becomes rowid 12
cur.execute("INSERT INTO test (val) VALUES (?)", ('testval',)) #becomes rowid 13

cur.execute("SELECT rowid, id, val FROM test")
print cur.fetchall()
</code></pre>

<p>Does auto-increment not support negative numbers like this? I couldn't find any mention of it in the SQLite documentation.</p>