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.

You can use an `AFTER INSERT` trigger to emulate a sequence in SQLite. See [my answer below](https://stackoverflow.com/a/69088585/1070129) for details. — emkey08, Sep 07 '21 at 13:21

score 42 · Accepted Answer · edited Oct 05 '13 at 18:40

42

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

edited Oct 05 '13 at 18:40

Ruben Bartelink

59,778
26
187
249

answered Aug 08 '11 at 12:44

Icarus

63,293
14
100
115

I used the dummy entry. and after real data was added to the table, I deleted it. – iliaden Aug 08 '11 at 13:12
The second statement didn't work for me. No such keyword as `ISNULL`. This one did: `INSERT INTO Log (id, rev_no, description) VALUES ((SELECT IFNULL(MAX(id), 0)) + 1 FROM Log), 'rev_Id', 'some description')` – Aug 28 '13 at 17:04
@DamianHickey thank you for the correction. `ISNULL` is from MS SQL Server which obviously doesn't work on SQLite. – Icarus Aug 28 '13 at 18:41
1

For future readers: The above snippet has 1 extra closing bracket on IFNULL. It should be like this: ` INSERT INTO Log (id, rev_no, description) VALUES ((SELECT IFNULL(MAX(id), 0) + 1 FROM Log), 'rev_Id', 'some description') ` – pedja Sep 06 '13 at 05:07
@pedja edited in - you can remove your comment and (I'll try to remember to remove mine :) – Ruben Bartelink Oct 05 '13 at 18:41

score 6 · Answer 2 · answered Aug 17 '16 at 13:45

6

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

answered Aug 17 '16 at 13:45

ZNorQ

61
1
1

I think this should be the correct answer, I never knew there is the hidden `rowid` field, thank you. I guess there is really no need create additional `id` field to keep track of auto-incremented ids. – CharlesC Nov 01 '19 at 18:22
rowid can be reused after delete, so it is not monotonic and cannot be used as a replace for autoincrement – folex Nov 03 '22 at 15:21

score 1 · Answer 3 · edited Dec 16 '13 at 22:37

My answer is very similar to Icarus's so I no need to mention it.

You can use Icarus's solution in a more advanced way if needed. Below is an example of seat availiabilty table for a train reservation system.

insert into Availiability (date,trainid,stationid,coach,seatno)
    values (
        '11-NOV-2013',
        12076,
        'SRR',
        1,
        (select max(seatno)+1
            from Availiability
            where date='11-NOV-2013'
                and trainid=12076
                and stationid='SRR'
                and coach=1)
    );

score 1 · Answer 4 · answered Sep 07 '21 at 12:53

You can use an AFTER INSERT trigger to emulate a sequence in SQLite (but note that numbers might be reused if rows are deleted). This will make your INSERT INTO statement a lot easier.

In the following example, the revision column will be auto-incremented (unless the INSERT INTO statement explicitly provides a value for it, of course):

CREATE TABLE test (
    id INTEGER PRIMARY KEY NOT NULL,
    revision INTEGER,
    description TEXT NOT NULL
);

CREATE TRIGGER auto_increment_trigger
    AFTER INSERT ON test
    WHEN new.revision IS NULL
    BEGIN
        UPDATE test
        SET revision = (SELECT IFNULL(MAX(revision), 0) + 1 FROM test)
        WHERE id = new.id;
    END;

Now you can simply insert a new row like this, and the revision column will be auto-incremented:

INSERT INTO test (description) VALUES ('some description');

score 1 · Answer 5 · answered Aug 08 '11 at 12:40

1

You could use a trigger (http://www.sqlite.org/lang_createtrigger.html) that checks the previous highest value and then increments it, or if you are doing your inserts through in a stored procedure, put that same logic in there.

answered Aug 08 '11 at 12:40

Peter vd Merwe

200
6

so... if I understand correctly, `CREATE TRIGGER update_revision INSERT OF single_string ON data BEGIN UPDATE data SET revision = (SELECT max(revision) from data ) + 1 WHERE single_string = ? END;` is the right syntax? – iliaden Aug 08 '11 at 12:44
Not sure about the syntax as I have not used SQLite, but you have the right idea there. Give it a go and see what happens :) – Peter vd Merwe Aug 08 '11 at 12:47

SQLite auto-increment non-primary key field

5 Answers5

Linked