SQLite3構文 列制約（foreign key references）

　外部キー。

成果物

• github

情報源

• https://sqlite.org/foreignkeys.html

一覧

• 有効化
• 即時 外部キー制約
  • references 表名(列名)
  • references 表名(列名,列名,...)
  • on delete
    • set null
    • set default
    • cascade
    • restrict
    • no action
  • on update
    • set null
    • set default
    • cascade
    • restrict
    • no action
  • on match
• 遅延 外部キー制約
  • references 表名(列名) deferrable initially deferred
  • references 表名(列名) deferrable initially immediate
  • pragma defer_foreign_keys
• 親テーブル名をリネームしてもOK
• 親テーブルはdropできない
• deleteとupdateのときにcascadeする

action一覧

no action

　親キーがデータベースから変更または削除されたときに、特別なアクションが実行しない。

restrict

　1つ以上の子キーがマップされている場合、親キーの削除（ON DELETE RESTRICTの場合）または変更（ON UPDATE RESTRICTの場合）を禁止する。

　通常の外部キー制約との違いは、RESTRICTアクションの処理は、フィールドが更新されるとすぐに発生すること。即時制約の場合のように現在のステートメントの最後ではなく、遅延制約の場合と同様に、現在のトランザクションの関連付けられている外部キー制約が延期されている場合でも、従属子キーを持つ親キーが削除または変更された場合、SQLiteがすぐにエラーを返す。

set null

　親キーが削除（ON DELETE SET NULLの場合）または変更（ON UPDATE SET NULLの場合）されると、マップした子テーブルのすべての行の子キー列親キーへのSQL NULL値を含むようにする。

set default

　set nullと同様。ただしセットする値はdefault値。

cascade

　親キーの削除または更新操作を各依存子キーに伝播します。「ON DELETE CASCADE」アクションの場合、これは、削除された親行に関連付けられた子テーブルの各行も削除されることを意味します。「ON UPDATE CASCADE」アクションの場合、各依存子キーに格納されている値が新しい親キー値に一致するように変更されることを意味します。

有効化

pragma foreign_keys = ON;
pragma foreign_keys = OFF;

即時 外部キー制約

references 表名(列名)

　違反させてみる。

0_0.sql

pragma foreign_keys = ON;
create table P(A integer references C(A));
create table C(A integer primary key);
insert into P values(0);

Error: near line 4: FOREIGN KEY constraint failed

　成功させてみる。

0_1.sql

pragma foreign_keys = ON;
create table P(A integer references C(A));
create table C(A integer primary key);
insert into C values(0);
insert into P values(0);

references 表名(列名,列名,...)

1_0.sql

pragma foreign_keys = ON;
create table P(A integer references C(A,B));
create table C(A integer primary key, B integer unique);
insert into P values(0,0);

Error: near line 2: foreign key on A should reference only one column of table C

　1列だけしか許さんと怒られる。論理的にそうだろうと思ってた。けど構文ツリーでは複数のキーを定義できるかのように書いてある。

• https://www.sqlite.org/syntax/column-constraint.html
  • https://www.sqlite.org/syntax/foreign-key-clause.html

　おそらく列制約でなく、表制約のときだけ、複数の列をセットできる？　だが、そもそも同一表内で複数列を外部キーにする意味がわからない。複合外部キーってこと？

　ミスマッチ？　たしか主キーか一意キーならいいはず。インデックスも必要だったか？

1_1.sql

pragma foreign_keys = ON;
create table P(A integer, B integer, foreign key (A,B) references C(A,B));
create table C(A integer primary key, B integer unique);
insert into P values(0,0);

Error: near line 5: foreign key mismatch - "P" referencing "C"

　両方とも主キーにすると「主キーは1つだけしか許さん」と怒られる。

1_2.sql

pragma foreign_keys = ON;
create table P(A integer, B integer, foreign key (A,B) references C(A,B));
create table C(A integer primary key, B integer primary key);
insert into P values(0,0);

Error: near line 4: table "C" has more than one primary key

　ならば主キーも表制約で定義してみる。

1_3.sql

pragma foreign_keys = ON;
create table P(A integer, B integer, foreign key (A,B) references C(A,B));
create table C(A integer, B integer, primary key(A,B));
insert into P values(0,0);

Error: near line 4: FOREIGN KEY constraint failed

　来た。外部キー制約違反。

　では、この違反を解消する。

1_4.sql

pragma foreign_keys = ON;
create table P(A integer, B integer, foreign key (A,B) references C(A,B));
create table C(A integer, B integer, primary key(A,B));
insert into C values(0,0);
insert into P values(0,0);

　エラーなし。OK。

　つまり、主キーを複数作るときは、列制約でなく表制約で定義すること。さもなくばエラーになる。

　もっとも、主キーを複数作る意味がわからないが。

references 表名(列名) on delete

references 表名(列名) on delete set null

2_0.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer references P(A) on delete set NULL);
insert into P values(0);
insert into C values(0);
delete from P;
select count(*) from C where A is NULL;

1

　親レコードを削除すると子の値がNULLになった。

references 表名(列名) on delete set default

2_1.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer default 99 references P(A) on delete set default);
insert into P values(0);
insert into C values(0);
delete from P;
select * from C;
select count(*) from C where A = 99;

Error: near line 6: FOREIGN KEY constraint failed
0
0

　おや、外部キー制約違反によりdeleteできなかった。これはdefault制約によりA列の値が99になったものの、親側に99のレコードがないため、外部キー制約違反になったものと思われる。

2_1_0.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer default 99 references P(A) on delete set default);
insert into P values(99);
insert into P values(0);
insert into C values(0);
delete from P where A=0;
select * from C;
select count(*) from C where A = 99;

99
1

　OK。親レコードが削除されたとき、デフォルト値になった。ただし、親レコードにデフォルト値のレコードがなければ外部キー制約違反になる。

　特定レコードは絶対に削除できないようにできないかな？　そうでもしないとエラーになりそう。

references 表名(列名) on delete cascade

2_2.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer references P(A) on delete cascade);
insert into P values(0);
insert into C values(0);
delete from P;
select * from C;
select count(*) from C;

0

　OK。親レコードが削除されたら、子レコードも削除される。これはよく使いそう。トリガーを作らなくてもいいのか。

references 表名(列名) on delete restrict

2_3.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer references P(A) on delete restrict);
insert into P values(0);
insert into C values(0);
delete from P;
select count(*) from P;
select * from C;
select count(*) from C;

Error: near line 6: FOREIGN KEY constraint failed
1
0
1

　OK。子レコードがあるとき親レコードの削除が禁止された。

references 表名(列名) on delete no action

2_4.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer references P(A) on delete no action);
insert into P values(0);
insert into C values(0);
delete from P;
select count(*) from P;
select * from C;
select count(*) from C;

Error: near line 6: FOREIGN KEY constraint failed
1
0
1

　あれ、restrictと同じ？

references 表名(列名) on update

references 表名(列名) on update set null

3_0.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer references P(A) on update set NULL);
insert into P values(0);
insert into C values(0);
update P set A = 1;
.echo on
select count(*) from C where A is NULL;

1

　親レコードを更新して子の値を消すと、その子の値がNULLになった。

references 表名(列名) on update set default

3_1.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer default 99 references P(A) on update set default);
insert into P values(0);
insert into C values(0);
update P set A = 1;
select * from C;
select count(*) from C where A = 99;

Error: near line 6: FOREIGN KEY constraint failed
0
0

　おや、外部キー制約違反によりupdateできなかった。これはdefault制約によりA列の値が99になったものの、親側に99のレコードがないため、外部キー制約違反になったものと思われる。

3_1_0.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer default 99 references P(A) on update set default);
insert into P values(99);
insert into P values(0);
insert into C values(0);
update P set A = 1 where A = 0;
select * from C;
select count(*) from C where A = 99;

99
1

　OK。親レコードが更新されたとき、その子の値がデフォルト値になった。ただし、親レコードにデフォルト値のレコードがなければ外部キー制約違反になる。

　特定レコードは絶対に削除できないようにできないかな？　そうでもしないとエラーになりそう。

references 表名(列名) on update cascade

3_2.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer references P(A) on update cascade);
insert into P values(0);
insert into C values(0);
select * from C;
update P set A = 1;
select * from C;

0
1

　OK。親レコードの値を更新したら、子レコードの値も更新された。これはよく使いそう。トリガーを作らなくても整合性を保てる。

references 表名(列名) on update restrict

3_3.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer references P(A) on update restrict);
insert into P values(0);
insert into C values(0);
update P set A = 1;
update C set A = 1 where A = 0;
select * from P;
select * from C;

Error: near line 6: FOREIGN KEY constraint failed
Error: near line 7: FOREIGN KEY constraint failed
0
0

　OK。子レコードがあるとき親レコードの更新が禁止された。

references 表名(列名) on update no action

3_4.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer references P(A) on update no action);
insert into P values(0);
insert into C values(0);
update P set A = 1;
update C set A = 1 where A = 0;
select * from P;
select * from C;

Error: near line 6: FOREIGN KEY constraint failed
Error: near line 7: FOREIGN KEY constraint failed
0
0

　あれ、restrictと同じ？

references 表名(列名) on match

• match句はサポートせず

遅延 外部キー制約

　遅延外部キー制約は、トランザクションがコミットされるまでエラーを遅延させる。

references 表名(列名) deferrable initially deferred

4_0.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer references P(A) deferrable initially deferred);
begin;
insert into C values(0); -- 外部キー制約違反（親がない）
select * from C;
commit;

0
Error: near line 7: FOREIGN KEY constraint failed

　本来ならinsertでエラー。だが、その後のselectを実行後にエラーになっている。これが遅延の効果。

references 表名(列名) deferrable initially immediate

　以下はすべてデフォルトと同じく即時外部キー制約である。

not deferrable initially deferred            -- an immediate foreign key constraint
not deferrable initially immediate           -- an immediate foreign key constraint
not deferrable                               -- an immediate foreign key constraint
deferrable initially immediate               -- an immediate foreign key constraint
deferrable                                   -- an immediate foreign key constraint

　即時はデフォルトなのでいつもどおり遅延せず即時エラー。

4_1.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer references P(A) deferrable initially immediate);
begin;
insert into C values(0); -- 外部キー制約違反（親がない）
select * from C;
commit;

Error: near line 5: FOREIGN KEY constraint failed

　insertの時点でエラー。

pragma defer_foreign_keys

• https://sqlite.org/foreignkeys.html#fk_deferred

• https://sqlite.org/pragma.html#pragma_defer_foreign_keys

　以下のようにするとすべての外部キー制約は、最も外側のトランザクションがコミットされるまで遅延される。

pragma defer_foreign_keys = ON;

4_2.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer references P(A));
begin;
pragma defer_foreign_keys = ON;
insert into C values(0); -- 外部キー制約違反（親がない）
select * from C;
commit;

0
Error: near line 8: FOREIGN KEY constraint failed

　エラーが遅延された。ポイントは、トランザクション中にpragma defer_foreign_keys = ON;すること。

　というか、これがあればテーブル定義でわざわざdeferrable initially deferredしなくてもいいよね。そもそも、遅延させたがる理由がよくわからん。

alter table rename to

　親テーブル名を変更しても、ちゃんと反映される。

5.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer references P(A));
insert into P values(0);
insert into C values(0);
alter table P rename to PP;
select * from C;

0

drop table

　親テーブルを削除する前に、子テーブルの全レコードを削除する。

6.sql

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer references P(A));
insert into P values(0);
insert into C values(0);
drop table P;
select count(*) from P;
select count(*) from C;

Error: near line 6: FOREIGN KEY constraint failed
1
1

　あれ、削除されていない。以下公式に削除されるって書いてあるんだが。

• https://sqlite.org/foreignkeys.html#fk_schemacommands

準備時に外部キー制約が有効になっている場合、 DROP TABLEコマンドは暗黙的なDELETEを実行して、削除する前にテーブルからすべての行を削除します。

　よく読んでみると以下。

即時外部キー制約に違反すると、DROP TABLEステートメントは失敗し、テーブルは削除されません。

　いや、それなら削除されなくて当然だわ。

　つまりdrop tableしても外部キー制約のon delete cascadeがないかぎり削除されないってことか。

cascade: delete and update

　deleteとupdateの両方のときにcascadeさせる方法。

• https://stackoverflow.com/questions/16380878/delete-and-update-cascading-doesnt-affect-on-child-table-in-sqlite

pragma foreign_keys = ON;
create table P(A integer primary key);
create table C(A integer references P(A) on update cascade on delete cascade);
insert into P values(0);
insert into C values(0);
select * from C;
update P set A = 1;
select * from C;

0
0
1

対象環境

• 2019-08-31
• 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）
• SQLite3構文　列制約（primary key）ベストプラクティス
• SQLite3構文　列制約（unique）
• SQLite3構文　列制約（not null）
• SQLite3構文　列制約（check）