SQLite auto-increment non-primary key field

user_752462 0
Viewed 21889

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.

auto-increment sqlite sql
5 Answers

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

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