SQLite auto-increment non-primary key field

Question

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 1

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

Answer 2

SQLite creates a unique row id (rowid) automatically. This field is usually left out when you use "select * ...", but you can fetch this id by using "select rowid,* ...". Be aware that according to the SQLite documentation, they discourage the use of autoincrement.

create table myTable ( code text, description text ); insert into myTable values ( 'X', 'some descr.' ); select rowid, * from myTable; 

:: Result will be; 1|X|some descr.

If you use this id as a foreign key, you can export rowid - AND import the correct value in order to keep data integrity;

insert into myTable values( rowid, code text, description text ) values ( 1894, 'X', 'some descr.' );