間違っているかもしれないが。
主キーが整数値のとき
create table テーブル名( 主キー名 integer primary key, 列名 型名 制約 );
主キーが文字列のとき
create table テーブル名( 主キー名 text primary key, 列名 型名 制約 ) without rowid;
もっと言えば、KeyValue型やドキュメント型のDBMSを使ったほうがいいだろう。理由は以下。
罠
これはダメ。
非integer
なprimary key
create table テーブル名( 主キー名 text primary key, 列名 型名 制約 );
primary key
はinteger
型のときのみ、B木で高速に動作する。他の型のときは「一意のインデックスを持つ通常のテーブル列」になるだけ。つまり、insert
時にこの列を省略したらNULL
が入ってしまう。
もし主キーの型をtext
にしたいなら、without rowid
テーブルにしてクラスタ化インデックスにすべき。
int
なprimary key
create table テーブル名( 主キー名 int primary key, 列名 型名 制約 );
int
ならアフィニティによりinteger
にしてくれる、と思うだろう。だが違う。前回確かめた。「整数アフィニティと一意のインデックスを持つ通常のテーブル列」になるだけ。つまり、insert
時にこの列を省略したらNULL
が入ってしまう。
int
はinteger
の省略形だと思った? プギャー!
desc
なinteger primary key
create table テーブル名( 主キー名 integer primary key, 列名 型名 制約 );
じゃあint
でなくinteger primary key
という文字列で定義すれば完璧だね! と思ったら大間違い。なんと、desc
がついたら「一意のインデックスを持つ通常のテーブル列」になってしまう。後方互換バグという名の仕様……。死んで? ねえ死んで?
設計ミス
これらを使うということはDB設計ミスなのでは?
autoincrement
「autoincrement
は、以前に挿入されたROWIDの最大値が上限値9223372036854775807
ならdatabase or disk is full
エラーにする(たとえ一度も使っていない番号があっても)」
主キー値を再利用しないことが目的。だが、その目的はこのDBとの整合性を保っていない他の記録との整合性をむりやり保つためであろう。つまり「その設計自体がまちがっている」。整合性を保つように設計すべきなのだ。よってautoincrement
を使わずに設計するのがベスト。
それでもautoincrement
を使ったほうが楽に実現できていい場合もあるだろう。そう思うなら使えばいい。おそらくID番号なんて一生使い切れないだろうし。番号が足りなくなったら対処することにすればいい。余計大変だろうけど。最初に整合性を保つしくみを作ったほうが、後戻り作業をするより楽だろうけど。
on conflict
シリーズ
主キーに対して使うべきか疑問。そもそもinsert
するときは主キーを省略して自動インクリメントした値を使うべき。update
のときは一切変更すべきでない。where
句の条件として使うべき。
このときコンフリクトを起こすことがない。よって使う必要なし。
主キーが衝突するのは、ユーザが任意の値を与えるときだ。それをしたいときは主キーが任意の文字列のときだろう。つまりwithout rowid
テーブルでクラスタ化インデックスを使ったとき。
だが、これは以前、少ないデータでしか高速に検索できないことが発覚した。B木でページ実装しているSQLite3の仕様によるものだ。それなら大人しくKeyValue型DBMSを使ったほうがいい。
よって、主キーにはon conflict
句を使う理由がない。
結論
SQLite3ならinteger primary key
一択。他はベストにあらず。当然の帰結か。
対象環境
- Raspbierry pi 3 Model B+
- Raspbian stretch 9.0 2018-11-13
- bash 4.4.12(1)-release
- SQLite 3.29.0
- MeCab 0.996ユーザ辞書
$ uname -a Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux
前回まで
- SQLite3学習 俯瞰まとめ
- SQLite3学習 環境構築まとめ
- SQLite3学習 インタフェースまとめ(C言語、CLI、対話モード、Tcl...)
- SQLite3学習 ドットコマンドまとめ
- SQLite3学習 JSON拡張まとめ
- SQLite3学習 FTSまとめ(ICU, MeCab)
- SQLite3学習 再帰クエリ(WITH RECURSIVE)
- SQLite3学習 R-Treeモジュール
- SQLite3学習 Geopoly(2次元ベクタ画像の生成)
- SQLite3学習 拡張関数(generate_series)
- SQLite3学習 拡張ライブラリ数学関数(extension-functions.c)
- SQLite3学習 謎と名前
- SQL文の分類(DDL,DML,TCL,DCL)
- SQL構文 alter(rename)
- SQL構文 alter(add column)概要
- SQL構文 alter(add column)制約
- SQL構文 alter(add column)sqlite_master変更しても反映されない
- SQL構文 alter(add column)スキーマ再定義(テーブル再作成による定義変更)
- SQL構文 analyze
- SQL構文 attach/detach
- SQLite3構文 begin,end,commit,rollback,savepoint(deferred,immediate,exclusive)
- SQLite3構文 コメント
- SQLite3構文 create/drop
- SQLite3構文 index(create/drop)
- SQLite3構文 table(create/drop)
- SQLite3構文 列制約(default)
- SQLite3構文 列制約(collate)
- SQLite3構文 列制約(primary key)