最も重要な制約。
成果物
情報源
一覧
primary key
integer primary key
primary key asc
primary key desc
primary key on conflict rollback
primary key on conflict abort
primary key on conflict fail
primary key on conflict ignore
primary key on conflict replace
primary key autoincrement
primary key
0.sql
create table T(A text primary key);
integer primary key
1.sql
create table T(A integer primary key);
WITHOUT ROWIDテーブルを除き、SQLiteテーブル内のすべての行には、テーブル内の行を一意に識別する64ビットの符号付き整数キーがあります。この整数は通常「rowid」と呼ばれます。
rowid値には、列名の代わりに、特別な大文字と小文字を区別しない名前「rowid」、「oid」、または「rowid」のいずれかを使用してアクセスできます。テーブルに「rowid」、「oid」または「rowid」という名前のユーザー定義列が含まれる場合、その名前は常に明示的に宣言された列を参照し、整数のrowid値を取得するために使用できません。
以前、以下のような謎の挙動を確認した。
これは型名がint
でなくinteger
としないと
動作
- 値が一意であるべき
null
は一意エラーにならないupdate
で重複するよう更新すると一意エラーになる
SQLite3はnull
値を許容するバグを抱えたままである(後方互換のため)。
一意エラー
1_0.sql
create table T(A integer primary key); insert into T values(0); insert into T values(0);
Error: near line 3: UNIQUE constraint failed: T.A
null
代入可&違反せず(後方互換用バグ)
1_1.sql
create table T(A integer primary key); insert into T values(NULL); insert into T values(NULL);
エラーなし。
update
で一意エラー
1_2.sql
create table T(A integer primary key); insert into T values(1); insert into T values(2); update T set A = 1 where A = 2;
Error: near line 4: UNIQUE constraint failed: T.A
エラーなし。
integer
以外だと自動インクリメントされずnull
になる
この原因については以下に書いてあった。
PRIMARY KEY列は、宣言された型名が正確に「INTEGER」である場合にのみ整数主キーになります。「INT」、「BIGINT」、「SHORT INTEGER」、または「UNSIGNED INTEGER」などの他の整数型名は、主キー列が、rowidのエイリアスとしてではなく、整数アフィニティと一意のインデックスを持つ通常のテーブル列として動作するようにします。
どうやらinteger primary key
という文字列で定義せねば主キー(PK)ではなく「一意のインデックスを持つ列」になるらしい。マジか……。primary key
って書いてるのに……。これはひどい。
あと、この言い方だと「integer primary key
はrowid
のエイリアス(別名定義)を作ること」らしい。そして、DBデータをファイル保存する仕組み上、高速検索するにはinteger primary key
であるべきらしい。だからinteger
以外でprimary key
を付与しても高速検索できる主キーにはならないと。
もし主キーが文字列であればwithout rowid
テーブルにして主キーの型をtext
にするのが最適なのだろう。たぶん。
0
や負数を与えて挿入できる
1_3.sql
create table T(A integer primary key); insert into T values(0); insert into T values(-1); select * from T;
-1 0
省略時の最小値は1
1_3_0.sql
create table T(A integer primary key, B int); insert into T(B) values(0); select A from T;
1
符号なしにして0
からにしてほしい。そのほうが2倍近く使えるのでは? 主キーを負数にしたくなるときはあるのか? 符号ありにしたのはなぜ? たぶんC言語のint
でunsigned
を付与せず定義できるよう符号ありにしたのかな? 知らんけど。
論理的に同じ
1_4.sql
CREATE TABLE T(a, b UNIQUE); CREATE TABLE U(a, b PRIMARY KEY); CREATE TABLE V(a, b); CREATE UNIQUE INDEX V_B ON V(b); insert into T(a) values('A'); insert into U(a) values('A'); insert into V(a) values('A'); .echo on select count(*) from T where b is NULL; # 1 select count(*) from U where b is NULL; # 1 select count(*) from V where b is NULL; # 1 .echo off
でも、integer
型にすると違う。primary key
のときだけは省略時にインクリメントした整数値を与えられる。
1_4_0.sql
create table T(a, b integer unique); create table U(a, b integer primary key); create table V(a, b integer); create unique index V_b on V(b); insert into T(a) values('A'); insert into U(a) values('A'); insert into V(a) values('A'); .echo on select count(*) from T where b is NULL; # 1 select count(*) from U where b is NULL; # 0 select count(*) from V where b is NULL; # 1 .echo off
わかりにくい。諸悪の根源であるバグを直してくれ。バグの後方互換とかいいから。
primary key asc
2.sql
create table T(A integer primary key asc, B text); insert into T(B) values('A'); insert into U(B) values('B'); insert into V(B) values('C'); select * from T;
1|A 2|B 3|C
では主キーの順序をめちゃくちゃにすると?
2_0.sql
create table T(A integer primary key asc, B text); insert into T values(2,'B'); insert into T values(1,'A'); insert into T values(3,'C'); select * from T;
1|A 2|B 3|C
おお、勝手にソートされた。これは一体いつソートされているの? ファイルに書き込むときにソートされた状態で書き換えるの? それともselect
した時点でソートして返すの?(ファイル状態は書込した順)
primary key desc
これは罠だ!
例外は、宣言された型「INTEGER」を持つ列の宣言に「PRIMARY KEY DESC」句が含まれる場合、ROWIDのエイリアスにならず、整数主キーとして分類されないことです。この癖は仕様ではありません。これは、SQLiteの初期バージョンのバグが原因です。ただし、バグを修正すると、後方互換性が失われる可能性があります。したがって、元の動作は保持され(文書化されています)、コーナーケースでの奇妙な動作は互換性の中断よりもはるかに優れているためです。
つまり、以下は主キーにならない。
2_1.sql
create table T(A integer primary key desc);
主キーであれば省略時にインクリメント整数値が代入される。主キーでなければnull
が代入される。どうなるか確認。
create table T(A integer primary key desc, B text); insert into T(B) values('A'); select * from T where A is NULL;
|A
はいnull
。数値じゃない。クソ。
primary key
と書いても主キーにならないことがある(後方互換バグ)- 型名が
integer
でない(int
でも不可) desc
句がある
- 型名が
やばい。バグによる細かい罠が辛い。SQL自体もエラー時のトランザクションが定義できない残念さがあったが、SQLite3も後方互換を理由に罠がある。しかも主キーというRDBMSにおいて最重要なところで……。
primary key on conflict rollback
on conflict
シリーズ。制約違反が起こったときの対処を記す。
見出しの件は、主キーが重複したらロールバックする。ただしトランザクションがない場合はabort
と同じ。
公式を翻訳したのが以下。
適用可能な制約違反が発生すると、ROLLBACK解決アルゴリズムはSQLITE_CONSTRAINTエラーで現在のSQLステートメントを中止し、現在のトランザクションをロールバックします。アクティブなトランザクションがない場合(すべてのコマンドで作成される暗黙のトランザクションを除く)、ROLLBACK解決アルゴリズムはABORTアルゴリズムと同じように機能します。
よくわからんが制約エラー時にロールバックしてくれるんじゃない?
int primary key
じつはこれも主キーにならない罠。
1_5.sql
create table T(A int primary key, B text); insert into T(B) values('A'); select * from T where A is NULL;
|A
int
ならアフィニティによりinteger
にしてくれる、と思うだろう。だが違う。「整数アフィニティと一意のインデックスを持つ通常のテーブル列」になるだけ。
つまり、insert
時に省略するとNULL
が代入されてしまう。自動インクリメントもしてくれない。
トランザクションなし
3_0.sql
create table T(A integer primary key on conflict rollback, B text); insert into T values(0, 'A'); insert into T values(0, 'B'); select * from T;
Error: near line 3: UNIQUE constraint failed: T.A
0|A
期待通り。
CLIにてエラー後の状態を確認してみる。
rm -f a.db \ sqlite3 a.db \ "create table T(A integer primary key on conflict rollback, B text);" \ "insert into T values(0, 'A');" \ "insert into T values(0, 'B');" \ "select * from T;" sqlite3 a.db "select * from T;"
Error: no such table: T
あれ、結果が違う。なぜ?
トランザクションの単位か? SQLファイルだとinsert
文で1行ずつ暗黙のトランザクションがあるのだろう。でも、CLIのときは複数のSQL文全体をトランザクションとした、とか?
トランザクションあり
3_1.sql
create table T(A integer primary key on conflict rollback, B text); begin; insert into T values(0, 'A'); insert into T values(0, 'B'); end; select * from T;
Error: near line 4: UNIQUE constraint failed: T.A Error: near line 5: cannot commit - no transaction is active
え、ちょ、死んでる。トランザクションはアクティブでない? なぜ。
もしや、SQLファイル内ではひとつのトランザクションになる? その内側にトランザクションをネストしたらバグるとか?
CLIエラー後の状態を確認してよう。
rm -f a.db \ sqlite3 a.db \ "begin;" \ "create table T(A integer primary key on conflict rollback, B text);" \ "insert into T values(0, 'A');" \ "insert into T values(0, 'B');" \ "end;" \ "select * from T;" sqlite3 a.db "select * from T;"
Error: no such table: T
あれ、エラーが出ない? そしてテーブルがない。さてはロールバックが成功したか? create
文までトランザクション内だから、ロールバックしてテーブルも消えたとか?
rm -f a.db \ sqlite3 a.db \ "create table T(A integer primary key on conflict rollback, B text);" \ "begin;" \ "insert into T values(0, 'A');" \ "insert into T values(0, 'B');" \ "end;" \ "select * from T;" sqlite3 a.db "select * from T;"
Error: no such table: T
え、変わらない。なにこれどうなってんの?
なら、きっとエラー前のやつを個別のトランザクションでコミット済みにすれば、それまでのは記録されるはず。
rm -f a.db \ sqlite3 a.db \ "begin;" \ "create table T(A integer primary key on conflict rollback, B text);" \ "end;" \ "begin;" \ "insert into T values(0, 'A');" \ "end;" \ "begin;" \ "insert into T values(0, 'B');" \ "end;" \ "select * from T;" sqlite3 a.db "select * from T;"
Error: no such table: T
変わらず……。どうなったのこれ? 頼むから動作確認できるコードをくれ。
ん? この感覚、前にも覚えがあるぞ。
CLIコマンドだと1プロセス。そこでエラーが出たらトランザクションがあろうがなかろうが、最初からすべて無かったことにされる。途中でのコミットすらロールバックされる。
ということか? 実際、
on conflict rollback
を消してみても変わらなかった。
rm -f a.db \ sqlite3 a.db \ "create table T(A integer primary key, B text);" \ "begin;" \ "insert into T values(0, 'A');" \ "insert into T values(0, 'B');" \ "end;" \ "select * from T;" sqlite3 a.db "select * from T;"
Error: no such table: T
rm -f a.db \ sqlite3 a.db \ "begin;" \ "create table T(A integer primary key, B text);" \ "end;" \ "begin;" \ "insert into T values(0, 'A');" \ "end;" \ "begin;" \ "insert into T values(0, 'B');" \ "end;" \ "select * from T;" sqlite3 a.db "select * from T;"
Error: no such table: T
対話モード(REPL)ではどうか
SQLファイルと同じだった。
sqlite3
3_0.sql
create table T(A integer primary key on conflict rollback, B text); insert into T values(0, 'A'); insert into T values(0, 'B');
Error: near line 3: UNIQUE constraint failed: T.A
select * from T;
0|A
成功。
次は失敗。
sqlite3
3_1.sql
create table T(A integer primary key on conflict rollback, B text); begin; insert into T values(0, 'A'); insert into T values(0, 'B');
Error: UNIQUE constraint failed: T.A
end;
Error: cannot commit - no transaction is active
select * from T;
primary key on conflict abort
abort
はデフォルトの動作。
4_0.sql
create table T(A integer primary key on conflict abort, B text); insert into T values(0, 'A'); insert into T values(0, 'B'); select * from T;
Error: near line 3: UNIQUE constraint failed: T.A
書く意味あんの? ない。まったくない。要らない子。
公式を翻訳したのが以下。
適用可能な制約違反が発生すると、ABORT解決アルゴリズムは、SQLITE_CONSTRAINTエラーで現在のSQLステートメントを中止し、現在のSQLステートメントによって行われた変更をすべて取り消します。ただし、同じトランザクション内の以前のSQLステートメントによる変更は保持され、トランザクションはアクティブのままです。これはデフォルトの動作であり、SQL標準で指定されている動作です。
どうせトランザクションを作ってもエラーになってすべて消されるんだろ? 一応確認する。
4_1.sql
create table T(A integer primary key on conflict abort, B text); begin; insert into T values(0, 'A'); insert into T values(0, 'B'); end; select * from T;
Error: near line 3: UNIQUE constraint failed: T.A
ほらみろ。もう騙されないぞ。
primary key on conflict fail
fail
はトランザクションを終了しないらしい。ただしそれ以降は変更されなくなるとか。それ、ロールバックと同じでは?
5_0.sql
create table T(A integer primary key on conflict fail, B text); insert into T values(0, 'A'); insert into T values(0, 'B'); select * from T;
Error: near line 3: UNIQUE constraint failed: T.A
0|A
公式によると以下。
適用可能な制約違反が発生すると、FAIL解決アルゴリズムは、SQLITE_CONSTRAINTエラーで現在のSQLステートメントを中止します。ただし、FAIL解決は、失敗したSQLステートメントの以前の変更をバックアウトせず、トランザクションを終了しません。たとえば、UPDATEステートメントが更新しようとする100番目の行で制約違反を検出した場合、最初の99行の変更は保持されますが、行100以降の変更は発生しません。
FAIL動作は、一意性、NOT NULL、およびCHECK制約に対してのみ機能します。外部キー制約違反がABORTが発生します。
エラー以降も変更できているのだが……。翻訳で狂ったのか?
5_1.sql
create table T(A integer primary key on conflict fail, B text); insert into T values(0, 'A'); insert into T values(0, 'B'); insert into T values(1, 'C'); select * from T;
Error: near line 3: UNIQUE constraint failed: T.A 0|A 1|C
トランザクションをつけてみるか。
5_2.sql
create table T(A integer primary key on conflict fail, B text); begin; insert into T values(0, 'A'); insert into T values(0, 'B'); insert into T values(1, 'C'); end; select * from T;
Error: near line 4: UNIQUE constraint failed: T.A 0|A 1|C
変わらず。
fail
はエラー箇所のSQL文だけを無視する。できるだけ多くのSQL
文を実行できる。できてしまう。もしトランザクションとして整合性を保つ単位であるなら、この動作は危険。
rollback
でエラー以降の箇所がどうなるか確認してみる。期待値は、エラー以降は一切挿入されないこと。
3_3.sql
create table T(A integer primary key on conflict rollback, B text); insert into T values(0, 'A'); insert into T values(0, 'B'); insert into T values(1, 'C'); select * from T;
Error: near line 3: UNIQUE constraint failed: T.A 0|A 1|C
あれ? 同じ。fail
との違いがわからない。どうやって使い分けていいかわからない……。
primary key on conflict ignore
6_0.sql
create table T(A integer primary key on conflict ignore, B text); insert into T values(0, 'A'); insert into T values(0, 'B'); select * from T;
0|A
エラーが出なかった。無視されたようだ。
公式によると以下。
適用可能な制約違反が発生すると、IGNORE解決アルゴリズムは、制約違反を含む1行をスキップし、何も問題がなかったかのようにSQLステートメントの後続の行の処理を続けます。制約違反を含む行の前後の他の行は、通常どおり挿入または更新されます。IGNORE競合解決アルゴリズムが使用されている場合、一意性、NOT NULL、およびUNIQUE制約エラーに対してエラーは返されません。ただし、IGNORE競合解決アルゴリズムは、外部キー制約エラーに対してABORTのように機能します。
6_1.sql
create table T(A integer primary key on conflict ignore, B text); begin; insert into T values(0, 'A'); insert into T values(0, 'B'); insert into T values(1, 'C'); end; select * from T;
0|A 1|C
rollback
ならエラーで中断される。トランザクションごとロールバックする。
3_4.sql
create table T(A integer primary key on conflict rollback, B text); insert into T values(0, 'A'); begin; insert into T values(1, 'B'); insert into T values(1, 'BB'); end; select * from T;
Error: near line 5: UNIQUE constraint failed: T.A
Error: near line 6: cannot commit - no transaction is active
0|A
そうこれこれ。あ、今まで最後のselect
結果が0件だったからエラーで終了していたと勘違いしていたんだな。やっぱこれでいいんだわ。
primary key on conflict replace
7_0.sql
create table T(A integer primary key on conflict replace, B text); insert into T values(0, 'A'); insert into T values(0, 'B'); select * from T;
0|B
前い値が次の値に置換された。replace
列の値が一致していたらupdate
文に置換されるイメージか。もしやこれが巷で噂のupsert
ってやつ?
公式によると以下。
場合UNIQUEまたはPRIMARY KEYの制約違反が発生し、REPLACEアルゴリズム前現在の行を挿入または更新する制約違反の原因となっている既存の行を削除し、コマンドが正常に実行を継続します。場合はNOT NULL制約違反が発生し、REPLACE紛争解決は、その列のデフォルト値にNULL値を置き換え、または列にデフォルト値がない場合、ABORTアルゴリズムが使用されています。場合はCHECK制約または外部キー制約違反が発生し、REPLACE競合解決アルゴリズムはABORTのように動作します。
制約を満たすためにREPLACE競合解決戦略が行を削除する場合、再帰トリガーが有効な場合にのみ 、削除トリガーが起動します。
updateフックは、 REPLACE競合解決戦略によって削除された行のために呼び出されません。また、REPLACEは変更カウンターを増分しません。この段落で定義されている例外的な動作は、将来のリリースで変更される可能性があります。
紛争解決とかなにそれ怖い。しかも過去を一切省みることなく問答無用で上書き。これが未来志向か。やったもん勝ちってことですね、わかります。
primary key autoincrement
AUTOINCREMENTの目的は、以前に削除された行からのROWIDの再利用を防止すること
というが、不正確。目的はそうなのだろうが、手段が不正確。正しくは主キーにautoincrement
があると、その値が上限に達したときdatabase or disk is full
エラーになる。以下、確かめた結果。
delete
した番号をすぐ再利用するか? しない
しない。
autoincrement
あり。
8_0.sql
create table T(A integer primary key autoincrement, B text); insert into T values('A'); insert into T values('B'); insert into T values('C'); delete from T where B = 'B'; insert into T values('D'); select * from T;
1|A 3|C 4|D
autoincrement
なし。
8_1.sql
create table T(A integer primary key, B text); insert into T(B) values('A'); insert into T(B) values('B'); insert into T(B) values('C'); delete from T where B = 'B'; insert into T(B) values('D'); select * from T;
1|A 3|C 4|D
同じ。autoincrement
がないほうはD
の主キー値が2
になってくれることを期待していたのだが。
上限値に達したとき、はじめて過去の番号に遡るのかもしれない。試してみよう。
上限値に達したときエラーになる
8_2.sql
create table T(A integer primary key autoincrement, B text); insert into T values(9223372036854775807, 'A'); insert into T(B) values('B'); select * from T;
Error: near line 3: database or disk is full
9223372036854775807|A
autoincrement
があるとdatabase or disk is full
となった。ご覧の通り、最初の1レコードだけで。おかしい。つまり主キーの値が最大値であるかどうかだけで判断しているのだろう。
この方法は公式がいう「以前に削除された行からのROWIDの再利用を防止する」とは言わない。なにせ一度たりとも使っていないIDがあるにも関わらず、database or disk is full
エラーになったのだから。おそらくそうする理由は、過去に使ったIDを保存してチェックするという重い処理やその実装を避けるためだろうが。
では正しくはどう説明するべきか?
「autoincrement
は、以前に挿入されたROWIDの最大値が上限値ならdatabase or disk is full
エラーにする(たとえ一度も使っていない番号があっても)」
ぜんぜん違うじゃん。なんかうまいことやってくれる風に言ってるけど、全然ちがうじゃん。
autoincrement
がないときは以下。適当な番号を取得している。
8_3.sql
create table T(A integer primary key, B text); insert into T values(9223372036854775807, 'A'); insert into T(B) values('B'); select * from T;
2372449283802917592|B 9223372036854775807|A
え、1
じゃないの? こうなるらしい。
sqlite_sequence
create table T(A integer primary key autoincrement, B text); insert into T(B) values('A'); select * from sqlite_sequence; insert into T(B) values('B'); select * from sqlite_sequence;
T|1
T|2
テーブル名と、そのテーブルが過去に作った最大rowid
。次にinsert
するときはこれにインクリメントした値となる。
あ、もしやinsert
で主キー省略時に値が1
になるのは、初期値が0
だから? それにインクリメントした値が1
だから?
どんなときにautoincrement
があると嬉しいの?
たとえば、主キー(ID)を持ったあるレコードを削除したとき。そのID値を参照する外部DBがあったとする。だが、その外部DBは別DBのためトリガーで削除することもできない。そんなときに整合性を簡単に保つために使える。と思う。
ただし、上限値を持ったレコードがあるとき、二度とinsert
できなくなる。database or disk is full
エラーのせいで。
autoincrement
って使わないほうが良くね?
- 上限値を持ったレコードがあると
insert
できない - 動作が遅くなる
あと、insert
するとき主キー値を指定していくつか飛ばしちゃったら、飛ばされた番号は二度と使われなくなってしまう。
所感
まとめないと読めたもんじゃない。
対象環境
- 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)