SQLite3 における INTEGER PRIMARY KEY と INT PRIMARY KEY の違い

ハマったのでメモ。


# 事象

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


# 原因

1. `INT PRIMARY KEY` と `INTEGER PRIMARY KEY` の違い
2. GUIクライアント [Lita](http://www.dehats.com/drupal/?q=node/58 "Lita - SQLite Administration Tool") のバグ

## INT PRIMARY KEY と INTEGER PRIMARY KEY の違い

stack over flow に [同様の質問](http://stackoverflow.com/questions/20289410/difference-between-int-primary-key-and-integer-primary-key-sqlite) があった。

回答に

> `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 の [マニュアル](http://www.sqlite.org/lang_createtable.html#rowid) にはもう少し詳しく書いてある（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 で以下のような簡単なスクリプトを書いて検証してみた。

```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: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` の値は同じである。

## GUIクライアントで確認

作成されたデータベースをいくつかのGUIクライアントで確認してみた。

**[Lita 1.6.4](http://www.dehats.com/drupal/?q=node/58 "Lita - SQLite Administration Tool")**

![20140809_sqlite_lita.png](https://qiita-image-store.s3.amazonaws.com/0/37208/abd922ad-441f-603c-459a-46bbe479cbf9.png)

主キーの値に `rowid` の値が表示されてしまっている。これはいけない。

今回の件とは直接関係ないが、作成したビューが見られないなど使い勝手もイマイチだった。

**[Sqliteman 1.2.2](http://sqliteman.yarpen.cz/ "Sqliteman -- sqlite developer/admin tool")**

![20140809_sqlite_sqliteman.png](https://qiita-image-store.s3.amazonaws.com/0/37208/e91e858a-7f48-bbc9-1639-8e11b8f89cbd.png)

主キーの値は正しく表示される。

**[SQLite maneger 0.8.1](https://addons.mozilla.org/ja/firefox/addon/sqlite-manager/ "SQLite Manager")**

![20140809_sqlite_sqlitemanager.png](https://qiita-image-store.s3.amazonaws.com/0/37208/ed75a8bc-c9a2-1474-1eb0-d236e3b1cf43.png)

主キーの値は正しく表示される。 `rowid` も表示してくれるので、主キーとは別に `rowid` が作成されていることも把握できる。


# まとめ

* `INTEGER PRIMARY KEY` で定義したフィールドは `rowid` のエイリアスとなる。
* `INT PRIMARY KEY` で定義したテーブルは、主キーとは別に `rowid` が作られる。
* 一部のGUIクライアントは `INT PRIMARY KEY` を正しく表示できない。

SQLite で整数型の主キーを作るときは `INTEGER` を使うようにしよう。