SQLite auto-increment non-primary key field

sql sqlite auto-increment
iliaden picture iliaden · Aug 8, 2011 · Viewed 18.1k times · Source

Is it possible to have a non-primary key to be auto-incremented with every insertion?

For example, I want to have a log, where every log entry has a primary key (for internal use), and a revision number ( a INT value that I want to be auto-incremented).

As a workaround, this could be done with a sequence, yet I believe that sequences are not supported in SQLite.

Answer

Icarus picture Icarus · Aug 8, 2011

You can do select max(id)+1 when you do the insertion.

For example:

INSERT INTO Log (id, rev_no, description) VALUES ((SELECT MAX(id) + 1 FROM log), 'rev_Id', 'some description')

Note that this will fail on an empty table since there won't be a record with id is 0 but you can either add a first dummy entry or change the sql statement to this:

INSERT INTO Log (id, rev_no, description) VALUES ((SELECT IFNULL(MAX(id), 0) + 1 FROM Log), 'rev_Id', 'some description')

Related questions

Reset the row number count in SQLite3/MySQL

I am using SQLite3. I load a table with say 30 rows using integer as Primary ID and it auto-increments. Now I delete all the rows from the table and then, reload some new information onto the table. Problem is: the …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More