Effective Way To Get Unused Rowid In Android/SQLite?

Suppose you do not want to recycle rowids for for table myTable below. For that you define: CREATE TABLE myTable ( id INTEGER PRIMARY KEY AUTOINCREMENT ,

Solution 1:

You can read the current value of that sequence from the sqlite_sequence table.

Solution 2:

As you identified, creating a row and deleting it is a valid approach. The other way would be to run a

SELECT id FROM myTable ORDER BY id DESC

Then look at the id of the first row returned and increment it by one. Be careful of concurrency though. If you insert something immediately after checking for an unused row than you might get some unexpected behavior.

Edit: Check out CL.'s answer. It's more practical. Plus you could update manually increment that table's sequence number so that you are guaranteed your row will be unused.

Solution 3:

I've never understood why someone would need to do this, but it can be done:

The SQLite documentation mentions that the auto_increment values are stored in a sqlite_sequence table which is created automatically in your database when you use auto_increments.

This S.O. answer explains one way to query the sqlite_sequence table to find the current maximum ID, you could then add 1 to it and 99.9% of the time this would be the next ID issued.

Solution 4:

So the answer is something like:

db.execSQL("UPDATE sqlite_sequence set seq=seq+1 WHERE name='myTable'");

SQLiteStatement st = db.compileStatement("SELECT seq from sqlite_sequence WHERE name='myTable'");
long v = st.simpleQueryForLong();
st.close();

Now v is unused rowid which anyone other will not get. Perhaps these statements must be in same transaction.

Not so simple for simple and quite common task?

Solution 5:

Inside the SQLiteOpenHelper you use, start a transaction. Insert some data and then rollback.

Such a way, you 'll be able to get the next row id, like this:

public long nextId() {
    long rowId = -1;

    SQLiteDatabase db = getWritableDatabase();
    db.beginTransaction();
    try {
        ContentValues values = new ContentValues();
        // fill values ...

        // insert a valid row into your table
        rowId = db.insert(TABLE_NAME, null, values);

        // NOTE: we don't call  db.setTransactionSuccessful()
        // so as to rollback and cancel the last changes
    } finally {
        db.endTransaction();
    }
    return rowId;
}