Search code examples
sqliteauto-increment

SQLite - How to access the AUTOINCREMENT value on INSERT?

I have a table with field ID being an auto-incrementing INTEGER PRIMARY KEY. If ID is due to be auto-incremented to some integer value n on the next INSERT, how can I access the value n in an INSERT statement?

In particular, I sometimes want a row to be initialized with a negative ID, that is ID = -n (the reason being I am also using ID as a boolean flag in addition to a primary key, so that I don't have to create a separate field just for the flag).

Solution

  • You can't. Row id generation only kicks in later and not when evaluating the expressions in your SQL. There's last_insert_rowid() but no current_insert_rowid().

    Also, overloading the rowid to contain other information beyond an identifier doesn't seem like a good idea. Just use other columns for additional information.