ハマったのでメモ。

事象

INT PRIMARY KEY を指定した主キーに非連続な値を追加したところ、
なぜか連続する値が追加されてしまう（ように見えた）。

原因

INT PRIMARY KEY と INTEGER PRIMARY KEY の違い
GUIクライアント Lita のバグ

INT PRIMARY KEY と INTEGER PRIMARY KEY の違い

stack over flow に同様の質問があった。

回答に

INTEGER is a special case in SQLite, when the database does not create a separate primary key, but reuses the ROWID column instead. When you use INT (or any other type that "maps" to INTEGER internally) a separate primary key is created.

とあり、 INTEGER とした場合は rowid が作られず、 INT （およびそれ以外の内部で INTEGER に変換される型）は rowid が作られるそうだ。

SQLite3 のマニュアルにはもう少し詳しく書いてある（stack over flow の回答にあるリンク先は SQLite2 のドキュメント）。

if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an "integer primary key". A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid.

主キーを INTEGER とした場合、そのカラムは integer primary key と呼ばれ、 rowid のエイリアスとなるとのこと。

GUIクライアント Lita のバグ

SQLite のGUIクライアントを試用している最中で、たまたま Lita を使っていたのだが、どうも Lita では INT PRIMARY KEY と指定したカラムは rowid の値が表示されてしまうようだ。

推測であるが、本来 rowid を参照するのは integer primary key だけであるが、それ以外の INTEGER に変換可能な型についても rowid を参照してしまっているのではないだろうか。

Lita のメンテナンスは4年以上されてないようなので、他のクライアントを使うことにする。

検証

コードで確認

Python で以下のような簡単なスクリプトを書いて検証してみた。

test_integer_primary_key.py

# -*- coding: utf-8-unix -*-
import sqlite3

def create_table_as_int(cursor):
    cursor.execute('''
    CREATE TABLE `as_int` (
        `key` INT NOT NULL,
        `value` INT NOT NULL,
        PRIMARY KEY (`key`)
    );''')

def create_table_as_integer(cursor):
    cursor.execute('''
    CREATE TABLE `as_integer` (
        `key` INTEGER NOT NULL,
        `value` INTEGER NOT NULL,
        PRIMARY KEY (`key`)
    );''')

def insert_record(cursor, table):
    data = [(1, 100), (2, 200), (4, 300), (5, 400), (10, 500), (101, 600)]
    sql = 'INSERT INTO `{0}` (`key`, `value`) VALUES (?, ?);'.format(table)
    for item in data:
        cursor.execute(sql, item)

def dump_table(cursor, table):
    sql = 'SELECT rowid ,key, value FROM {0};'.format(table)

    print('*** {0} ***'.format(table))
    for row in cursor.execute(sql):
        print(row)

def main():
    db = sqlite3.connect('./sample.sqlite')
    cursor = db.cursor()

    create_table_as_int(cursor)
    insert_record(cursor, 'as_int')
    dump_table(cursor, 'as_int')

    create_table_as_integer(cursor)
    insert_record(cursor, 'as_integer')
    dump_table(cursor, 'as_integer')

    db.commit()
    db.close()
    return 0


if __name__ == '__main__':
    main()

console

% python3 test_integer_primary_key.py
*** as_int ***
(1, 1, 100)
(2, 2, 200)
(3, 4, 300)
(4, 5, 400)
(5, 10, 500)
(6, 101, 600)
*** as_integer ***
(1, 1, 100)
(2, 2, 200)
(4, 4, 300)
(5, 5, 400)
(10, 10, 500)
(101, 101, 600)

INT とした場合は主キーとは別に rowid が連番で作られている。一方 INTEGER とした場合は主キーと rowid の値は同じである。