やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

📌 ？
トップ > SQLite3 > SQLite3構文　列制約（primary key）ベストプラクティス

SQLite3構文　列制約（primary key）ベストプラクティス

SQLite3

　間違っているかもしれないが。

主キーが整数値のとき

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一択。他はベストにあらず。当然の帰結か。

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで