Sqlite Starting Rowid Of 0

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

Solution 1:

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.

Solution 2:

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

Here is the code

longdbid= getDbid();
    ContentValuesvalues=newContentValues();
    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, newPair<>(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.