2

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.

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.

The bottom-line question:

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

Some side information:

I found a similar question here and played with some AUTOINCREMENT settings: Set start value for AUTOINCREMENT in SQLite

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

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()

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.

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()

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

Community
  • 1
  • 1
crdavis
  • 21,005
  • 3
  • 14
  • 9

2 Answers2

3

The documentation says that, with AUTOINCREMENT,

the ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table.

So the algorithm looks not only at the value in the sqlite_sequence table, but also at the last row in the table, and uses the larger of these two values.

When the table is empty, the largest actual rowid is instead assumed to be zero. This is done so that the first inserted rowid becomes 1.

Therefore, the only way to generate a rowid less than one is to have another row already in the table.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanks for that info. I was under the impression that the sqlite_sequence table was the only source used but this makes sense. – crdavis Jan 29 '16 at 13:14
-1

I was using sqlite in java and unintentionally go the first row to have ROWID=0.

Here is the code

    long dbid = getDbid();
    ContentValues values = new ContentValues();
    values.put(KEY_ROWID, dbId); // Line that cause ROWID == 0 when dbid == 0
    values.put(KEY_KEY, key);
    values.put(KEY_VALUE, value);
    if (dbId == 0) {
        dbId = dbase.insert(PREFERENCES_TABLE, null, values);
        map_.put(key, new Pair<>(dbId, value));
    }
    else
        dbase.update(PREFERENCES_TABLE, values, KEY_ROWID + "=" + dbId, null);

The intent was to have zero be an uninitialized value but insert was returning zero. I didn't want the first ROWID to equal zero so I removed the line and insert returned one.

Matt Morrise
  • 1