SQLite AUTOINCREMENT column counter behavior with ON CONFLICT IGNORE clause
Question: When creating a small app using SQLite I've noticed a strange thing (for me)
with autoincrement columns counter algorithm behavior.

For instance, let's try to create database with the following schema

    
    
    CREATE TABLE numbers (num INTEGER UNIQUE ON CONFLICT IGNORE);
    

and a small Python script

    
    
    import sqlite3
    
    con = sqlite3.connect('db.sqlite3')
    cur = con.cursor()
    
    def values():
        for i in xrange(1, 3):
            yield (i,)
    try:
        cur.executemany('INSERT INTO numbers (num) VALUES (?)', values())
    except sqlite3.DatabaseError, err:
        print u'Error: ', err
    else:
        con.commit()
        print u'Number of added rows: %d' % cur.rowcount
    
    cur.close()
    con.close()
    

Let's run script for three times. Last time with different values() output,
xrange(3,5) for example. So, we get output

    
    
    Number of added rows: 2
    Number of added rows: 0
    Number of added rows: 2
    

Ok, then let's check our database and everything seems as it should

    
    
    $ sqlite3 db.sqlite3 'select rowid, * from numbers'
    1|1
    2|2
    3|3
    4|4
    

Then try to create database with adding an autoincrement alias to system rowid
column.

    
    
    CREATE TABLE numbers (id INTEGER PRIMARY KEY AUTOINCREMENT, num INTEGER UNIQUE ON CONFLICT IGNORE);
    

Then do the same thing as above and check the rows.

    
    
    $ sqlite3 db.sqlite3 'select rowid, * from numbers'
    1|1|1
    2|2|2
    5|5|3 <- sqlite jumped over intermediate counter values for rowid and id column
    6|6|4
    

SQLite saves intermediate counter values for rowid and alias autoincrement
column when using IGNORE constraint voilation resolution algorithm for a table
and skips them when assigning a new autoincrement value. Why?


Answer: Because:

  1. sqlite sees that a `ROWID` has not been specified and generates one. For regular `INTEGER PRIMARY KEY` cases it's basically `MAX(ROWID)+1`. For `INTEGER PRIMARY KEY AUTOINCREMENT` cases a new number that has never before been used is picked from the special `sqlite_sequence` table. This table holds the table `name` and `seq` value for autoincrement row ids.

  2. An insert is attempted. The unique constraint is violated so the `ON CONFLICT IGNORE` resolution takes place. Effectively nothing is done and no error is raised. However, altering `sqlite_sequence` was still successful and will be committed once the transaction completes.

Reference: <http://www.sqlite.org/autoinc.html>

