CODEWITHSUNDEEP
sqlsqliteauto-increment
iliaden
iliaden

Reputation: 3889

SQLite auto-increment non-primary key field

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.

Upvotes: 33

Views: 26696

Answers (5)

emkey08
emkey08

Reputation: 6241

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

Upvotes: 2

ZNorQ
ZNorQ

Reputation: 61

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

Upvotes: 6

Navin
Navin

Reputation: 11

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

Upvotes: 1

Icarus
Icarus

Reputation: 63970

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

Upvotes: 43

Peter vd Merwe
Peter vd Merwe

Reputation: 300

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.

Upvotes: 1

Related Questions