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.

Upi

You can use an AFTER INSERT trigger to emulate a sequence in SQLite. See my answer below for details. – Schriever

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

Raymonraymond

I used the dummy entry. and after real data was added to the table, I deleted it. – Upi

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

@DamianHickey thank you for the correction. ISNULL is from MS SQL Server which obviously doesn't work on SQLite. – Raymonraymond

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') ` – Punk

@Punk edited in - you can remove your comment and (I'll try to remember to remove mine :) – Tristan

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

Phallus

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. – Entomophilous

rowid can be reused after delete, so it is not monotonic and cannot be used as a replace for autoincrement – Erfurt

Answer 3

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

Schriever

Answer 4

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.

Wallop

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? – Upi

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 :) – Wallop

Answer 5

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

Charette