やってみる

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

SQLite3構文 列制約(foreign key references)

 外部キー。

成果物

情報源

一覧

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

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

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

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

遅延 外部キー制約

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

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

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

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

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

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

 よく読んでみると以下。

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

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

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

cascade: delete and update

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

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

対象環境

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

前回まで