あるテーブルの列が別のテーブルの列を参照しているとき、参照するテーブルのキーを参照されるテーブルのキーの外部キーと定義されます。外部キーを 持つテーブルのキーを子テーブルの子キー、参照されるテーブルのキーを親テーブルの親キーと呼ばれております。リレーショナルデータベースでは、テーブル 同士の関係性を構築する為に、外部キーが使われます。
sqliteにおける外部キー制約の概要
外部キー制約は、sqliteバージョン3.6.19(2009-10-14)で導入されました。これは、子キーが参照する親キーの存在を強制し、参照整合性が確保され、親子関係が遮断されるレコードが無いことを保証します。
利用するに当たって以下のようなことに留意する必要があります。
外部キーを有効にする
sqlite> PRAGMA foreign_keys; -- デフォルトでは無効になっている
0
sqlite> PRAGMA foreign_keys = ON; -- 有効にする
sqlite> PRAGMA foreign_keys;
1尚、複数ステートメントのトランザクション の途中で外部キー制約を有効または無効にすることはできません(SQLite が自動コミット モードでない場合)。
外部キーの宣言方法
外部キーの宣言は、列定義の後方での記述とインラインの記述の二通りがあります。
列定義の後方で記述
CREATE TABLE table_name (column_name1 [data_type] ... ,
column_name2 [data_type] ... ,
. .
. ,
foreign key (set_column_name)
references parent_table_name (parent_column_name)
-- 複数指定の場合はカンマなしでつづけます
);sqlite> create table p (p_col1 integer primary key,
...> p_col2 text unique not null,
...> p_col3 integer not null
...> );
sqlite> create table c (c_col1 integer primary key,
...> c_col2 text,
...> cr_col3 integer not null,
...> foreign key (cr_col3)
...> references p (p_col1)
...> );インラインで記述
sqlite> create table p (p_col1 integer primary key,
...> p_col2 text unique not null,
...> p_col3 integer not null
...> );
sqlite> create table c_inline (c_col1 integer primary key,
...> c_col2 text,
...> cr_col3 integer not null
...> references p (p_col1)
...> );親キーとなる列の条件
親キーは親テーブルの主キー(rowidを除く)またはunique制約キーである必要があります。
sqlite> create table p (p_col1 integer primary key,
...> p_col2 text unique not null, -- 親キーとなる
...> p_col3 integer not null
...> );
sqlite> create table c_ref_unique (c_col1 integer primary key,
...> c_col2 text,
...> cr_col3 text not null,
...> foreign key (cr_col3)
...> references p (p_col2) -- 親テーブルのuniqueキー参照
...> );親キーが親テーブルの主キーである場合は列名を省略できる
上記の場合は、子キー参照の references p (p_col1) が references p に省略できます。
親テーブルに存在しない親キーの外部キーは作成されますが機能しません。
sqlite> insert into p values (null, 'english', '米国、イギリス、オーストラリア');
sqlite> insert into p values (null, 'chinese', '中国、台湾、香港');
sqlite> insert into p values (null, 'latin', 'ポルトガル、ブラジル');
sqlite> insert into p values (null, 'japanese', '日本');
sqlite> .mode column
sqlite> select p_col1 as id, p_col2 as language, p_col3 as country from p;
id language country
-- -------- ---------------
1 english 米国、イギリス、オーストラリア
2 chinese 中国、台湾、香港
3 latin ポルトガル、ブラジル
4 japanese 日本
sqlite> insert into c values (null, 'takai', 1);
sqlite> insert into c values (null, 'takai', 5);
sqlite> select c_col1 as id, c_col2 as name, cr_col3 as skill from c;
id name skill
-- ----- -----
1 takai 1
2 takai 5 -- 存在しない skill number.
sqlite> select c_col1 as id, c_col2 as name, p_col2 as skill_language from c
...> inner join p
...> on cr_col3=p_col1
...> ;
id name skill_language
-- ----- --------------
1 takai english
-- id 2 は結合できないが cテーブルにはレコードとして残っている。外部キー制約を使用しないと、整合性を考慮しないレコードが挿入されることがあります。
サンプルデータベースで外部キー制約を確認する
下図のような親子階層関係であるテーブルをサンプルとして取り上げてみることにします。
テーブル構成
---- parent_base as my_friends p_col1 as id p_col2 as name p_col3 as mail_address
sqlite> create table parent_base (
...> p_col1 integer primary key not null,
...> p_col2 text unique not null,
...> p_col3 text
...>);
---- child_base as student c_col1 as id c_col2 as myfriend_id c_col3 as school_name
sqlite> create table child_base (
...> c_col1 integer primary key not null,
...> cr_col2 integer unique not null
...> references parent_base,
...> c_col3 text
...>);
---- grand_child_base as global_examination gc_col1 as examination_name, pcr_col2 as student_id, gc_col3 score
sqlite> create table grand_child_base (
...> gc_col1 text not null,
...> gcr_col2 integer not null,
...> gc_col3 integer not null,
...> primary key (gc_col1, gcr_col2),
...> foreign key (gcr_col2)
...> references child_base(cr_col2)
...>);
---- ggrand_child_base as curiculum ggcr_col1 as examination_name, ggcr_col2 as student_id, ggcr_col3 as curiculum_name, ggc_col4 score
sqlite> create table ggrand_child_base (
...> ggcr_col1 text not null,
...> ggcr_col2 integer not null,
...> ggc_col3 text not null,
...> ggc_col4 integer not null,
...> primary key (ggcr_col1, ggcr_col2),
...> foreign key (ggcr_col1, ggcr_col2)
...> references grand_child_base(gc_col1, gcr_col2)
...>);
-- (ggcr_col1, ggcr_col2)と(gc_col1, gcr_col2)が対応している親テーブルと子テーブルは外部キー制約で関係性をもっています。parent テーブル以下の子テーブルは、すべてparent テーブルのp_col1を参照する構成となっております。
外部キー制約でinsert 、update 、delete sql文を発行してみる。
---- parent_base as myfriend テーブルにデータを入力
sqlite> insert into parent_base values(null, 'takai', 'pecker@unknown.com');
sqlite> insert into parent_base values(null, 'arai', 'pecker2@unknown.com');
sqlite> insert into parent_base values(null, 'kimura', 'pecker3@unknown.com');
sqlite> insert into parent_base values(null, 'wada', 'pecker4@unknown.com');
sqlite> insert into parent_base values(null, 'endou', 'pecker5@unknown.com');
sqlite> .mode column
sqlite> select * from parent_base;
p_col1 p_col2 p_col3
------ ------ -------------------
1 takai pecker@unknown.com
2 arai pecker2@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
---- child as student テーブルにデータを入力してみる
sqlite> insert into child_base values(null, 1, 'a_school');
sqlite> insert into child_base values(null, 3, 'b_school');
sqlite> insert into child_base values(null, 4, 'c_school');
sqlite> insert into child_base values(null, 5, 'd_school');
sqlite> insert into child_base values(null, 6, 'e_school');
Error: FOREIGN KEY constraint failed -- cr_col2の親キーp_col1には 6 がない insertできない
---- child as student update fail.
sqlite> update child_base set cr_col2=6 where c_col1=3;
Error: FOREIGN KEY constraint failed -- 上記同様に update できない
---- insert されているchildデータを確認する
sqlite> select * from child_base;
c_col1 cr_col2 c_col3
------ ------- --------
1 1 a_school
2 3 b_scholl
3 4 c_school
4 5 d_school
---- child の親キーがあるparentレコードを削除してみる
sqlite> delete from parent_base where p_col1=3;
Error: FOREIGN KEY constraint failed -- 親キーは削除できない
sqlite> update parent_base set p_col1=30 where p_col1=3;
Error: FOREIGN KEY constraint failed -- 親キーは更新できない
---- parentテーブル と childテーブル を内部結合してみる
sqlite> select * from child_base inner join parent_base on cr_col2=p_col1;
c_col1 cr_col2 c_col3 p_col1 p_col2 p_col3
------ ------- -------- ------ ------ -------------------
1 1 a_school 1 takai pecker@unknown.com
2 3 b_school 3 kimura pecker3@unknown.com
3 4 c_school 4 wada pecker4@unknown.com
4 5 d_school 5 endou pecker5@unknown.com
---- 外部キーの親キーになっていないparentレコードを削除してみる
sqlite> delete from parent_base where p_col1=2; -- delete (update) 可能である。
sqlite> select * from parent_base;
p_col1 p_col2 p_col3
------ ------ -------------------
1 takai pecker@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
---- parentテーブルを削除してみる
sqlite> drop table parent_base;
Error: FOREIGN KEY constraint failed
-- 親キーとなっている列が含まれる場合はテーブルを削除できない
---- grand_child_baseテーブル にデータを挿入する
sqlite> insert into grand_child_base values('first_exami2020', 3, 250);
sqlite> insert into grand_child_base values('second_exami2020', 5, 200);
sqlite> insert into grand_child_base values('second_exami2020', 2, 200);
Error: FOREIGN KEY constraint failed -- 親テーブルchild_baseテーブルの親キーに 2 の値が存在しない
sqlite> insert into grand_child_base values('second_exami2020', 1, 280);
sqlite> insert into grand_child_base values('last_exami2020', 1, 190);
sqlite> insert into grand_child_base values('last_exami2020', 1, 100);
Error: UNIQUE constraint failed: -- grand_child_base.gc_col1, grand_child_base.gcr_col2 複合キーがprimary keyであるため、uniqueである必要がある
sqlite> insert into grand_child_base values('last_exami2020', 4, 100);
sqlite> select * from grand_child_base;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
first_exami2020 3 250
second_exami2020 5 200
second_exami2020 1 280
last_exami2020 1 190
last_exami2020 4 100
---- grand_childテーブルにある外部キーの親キーを削除してみる
sqlite> delete from child_base where cr_col2=3;
Error: FOREIGN KEY constraint failed -- 親キーのレコードは削除できない
存在しない親キーを含むsqlステートメント発行はすべて失敗してしまう。
外部キーにインデックスをつけて、検索効率を上げる
外部キーは親キーに紐づけされています。子キーに対して、insert update delete する sql statementが発生した場合、内部的には検索照合処理が発生します。検索対象の外部キー列にインデックスをつけることにより、検索コストを削減でき ることが期待されます。（但し、レコード数が少ない場合は、恩恵を受けない。）
create index childindex on child_base(cr_col2);複合外部キー制約 Composite Foreign Key Constraints
複合外部キー制約は、子キーと親キーが相互に複合キーである外部キー制約です。ggrand_childの子キーとgran_child の親キーがその制約に当てはまります。
---- 親キーを含むgrand_chile_baseテーブルの構成
sqlite> .schema grand_child_base
CREATE TABLE grand_child_base (
gc_col1 text not null,
gcr_col2 integer not null,
gc_col3 integer not null,
primary key (gc_col1, gcr_col2), -- 親キーが複合キーになっている
foreign key (gcr_col2)
references child_base(cr_col2)
);
---- 子キーを含むggrand_child_baseテーブルの構成
sqlite> .schema ggrand_child_base
CREATE TABLE ggrand_child_base (
ggcr_col1 text not null,
ggcr_col2 integer not null,
ggc_col3 text not null,
ggc_col4 integer not null,
primary key (ggcr_col1, ggcr_col2),
foreign key (ggcr_col1, ggcr_col2) -- 子キーも親キーに対応して複合キーになっている
references grand_child(gc_col1, gcr_col2)
);
---- 挿入されているgrand_child_baseテーブルのレコードを表示
sqlite> select * from grand_child_base;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
first_exami2020 3 250
second_exami2020 5 200 -- ①に対する親キー gc_col1 gcr_col2
second_exami2020 1 280
last_exami2020 1 190
last_exami2020 4 100
---- ggrand_child_base テーブルにレコードを入力してみる
sqlite> insert into ggrand_child_base values('second_exami2020', 5, 'mathematics', 90); -- ①
sqlite> insert into ggrand_child_base values('second_exami2020', 2, 'mathematics', 80);
Error: FOREIGN KEY constraint failed -- 'second_exami2020', 2 の親キーは存在しない
sqlite> select * from ggrand_child_base;
ggcr_col1 ggcr_col2 ggc_col3 gc_col4
---------------- --------- ----------- -------
second_exami2020 5 mathematics 90 ON DELETE および ON UPDATE アクション
外部キーの ON DELETE 句と ON UPDATE 句は、親テーブルから行を削除するとき (ON DELETE)、既存の行の親キー値を変更するとき (ON UPDATE) に実行されるアクションです。
references table_name(column_name) on delete or update action_nameアクションの種類は以下のとおりになっています。アクションが明示的に指定されていない場合、デフォルトは「NO ACTION」です。
- NO ACTION: 親キーが変更されたり、データベースから削除されたりしても、特別なアクションは実行されません。
- RESTRICT: 親キーを削除 (ON DELETE RESTRICT の場合) または変更 (ON UPDATE RESTRICT の場合) することをブロックすることを意味します。通常の外部キー制約（即時外部キー制約）も同じようにブロックしますが、ステートメント実行終了時ではなく、 フィールドが更新されるとすぐに操作をブロックすることです。
- SET NULL: 親キーが削除(ON DELETE SET NULLの場合)または変更(ON UPDATE SET NULLの場合)されると、その親にマッピングされている子レコードの外部キーが null に設定されます。これらのフィールドが NOT NULL に設定されている場合、エラーが発生します。
- SET DEFAULT: 外部キー列に設定されているデフォルト値が適用されます。デフォルト値が無い場合はnullがセットされます。但し、親キーにデフォルト値が登録されている必要がある場合もあります。
- CASCADE: 親キーに対する削除または更新操作を各従属子キーに伝播します。
親キーの更新に子キーを連動させる on update cascade
---- grand_child_baseの外部キーをchild_baseの親キーの更新に連動させるように書き換えた grand_child_cascade_uテーブルを生成
sqlite> create table grand_child_cascade_u (
...> gc_col1 text not null,
...> gcr_col2 integer not null,
...> gc_col3 integer not null,
...> primary key (gc_col1, gcr_col2),
...> foreign key (gcr_col2) references child_base(cr_col2) on update cascade
...> );
---- grand_child_cascade_uテーブルにデータを挿入する
sqlite> insert into grand_child_cascade_u values('first_exami2021', 5, 150);
sqlite> select * from grand_child_cascade_u;
gc_col1 gcr_col2 gc_col3
--------------- -------- -------
first_exami2021 5 150
---- その親キーであるchild_baseテーブルの cr_col2列を更新してみる
sqlite> update child_base set cr_col2=2 where cr_col2=5;
Error: FOREIGN KEY constraint failed -- 親キーが更新できない
sqlite> update child_base set cr_col2=2 where c_col3='d_school';
Error: FOREIGN KEY constraint failed -- 親テーブル(child)の親キー以外の列も更新できない
-- child_baseの外部キーであるcr_col2(parent_baseテーブルを親キーとしている)がgrand_childの外部キーになっているので、失敗する。
---- child_baseにもon update cascade アクションをつけたchild_cascade_u テーブルを生成
sqlite> create table child_cascade_u (
...> c_col1 integer primary key not null,
...> cr_col2 integer not null unique
...> references parent_base on update cascade,
...> c_col3 text
...> );
---- grand_child_cascade_u テーブルの references も child_cascade_uに換える
sqlite> drop table grand_child_cascade_u;
sqlite> create table grand_child_cascade_u (
...> gc_col1 text not null,
...> gcr_col2 integer not null,
...> gc_col3 integer not null,
...> primary key (gc_col1, gcr_col2),
...> foreign key (gcr_col2)
...> references child_cascade_u(cr_col2) on update cascade -- 書き換え
...> );
---- child_cascade_uテーブルにデータを挿入する
sqlite> insert into child_cascade_u values(null, 5, 'd_school');
sqlite> select * from child_cascade_u;
c_col1 cr_col2 c_col3
------ ------- --------
1 5 d_school
sqlite> select * from parent_base;
p_col1 p_col2 p_col3
------ ------ -------------------
1 takai pecker@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
---- parent_baseテーブルの child_cascade_u 外部キーに対する親キーを更新してみる
sqlite> update parent_base set p_col1=6 where p_col1=5;
Error: FOREIGN KEY constraint failed
---- parent_base の p_col1はchild_cascade_uの外部キーの親キーになっている一方、child_baseの親キーでもあるので、child_base外部キー制約をはずしたparent2を新たに生成する
---- child_casucade_u テーブルの referencesもparent2に換える
sqlite> create table parent2 (
...> p_col1 integer primary key not null,
...> p_col2 text unique not null,
...> p_col3 text
...> );
sqlite> drop table child_cascade_u;
sqlite> create table child_cascade_u (
...> c_col1 integer primary key not null,
...> cr_col2 integer not null unique
...> references parent2 on update cascade, -- 変更
...> c_col3 text
...> );
---- データを挿入する
sqlite> insert into parent2 values(null, 'takai', 'pecker@unknown.com');
sqlite> insert into parent2 values(null, 'arai', 'pecker2@unknown.com');
sqlite> insert into parent2 values(null, 'kimura', 'pecker3@unknown.com');
sqlite> insert into parent2 values(null, 'wada', 'pecker4@unknown.com');
sqlite> insert into parent2 values(null, 'endou', 'pecker5@unknown.com');
sqlite> select * from parent2;
p_col1 p_col2 p_col3
------ ------ ------------------
1 takai pecker@unknown.com
2 arai pecker2@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
sqlite> insert into child_cascade_u values(null, 1, 'a_school');
sqlite> select * from child_cascade_u;
c_col1 cr_col2 c_col3
------ ------- --------
1 1 a_school
---- child_cascade_u 外部キーの親キーを更新してみる
sqlite> update parent2 set p_col1=10 where p_col1=1;
sqlite> select * from parent2;
p_col1 p_col2 p_col3
------ ------ ------------------
2 arai pecker2@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
10 takai pecker@unknown.com -- 1 -> 10 に更新されている
sqlite> select * from child_cascade_u;
c_col1 cr_col2 c_col3
------ ------- --------
1 10 a_school -- 更新されている
---- grand_child_casucade_u テーブルも同じように換えてみる
sqlite> drop table grand_child_cascade_u;
sqlite> create table grand_child_cascade_u (
...> gc_col1 text not null,
...> gcr_col2 integer not null,
...> gc_col3 integer not null,
...> primary key (gc_col1, gcr_col2),
...> foreign key (gcr_col2)
...> references child_cascade_u(cr_col2) on update cascade
...> );
---- 存在する親キーの値を持つレコードを入力する
sqlite> insert into grand_child_cascade_u values(
...> 'second_exami2020', 10, 200);
sqlite> select * from grand_child_cascade_u;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
second_exami2020 10 200
---- grand_child_cascade_u テーブルの外部キーから見て、親キーの親キーであるparent2の親キーの値を更新してみる
sqlite> update parent2 set p_col1=100 where p_col1=10;
sqlite> select * from parent2;
p_col1 p_col2 p_col3
------ ------ ------------------
2 arai pecker2@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
100 takai pecker@unknown.com -- 更新されている
sqlite> select * from child_cascade_u;
c_col1 cr_col2 c_col3
------ ------- --------
1 100 a_school -- 10 から 100 に更新されている
---- child_cascade_uテーブルの外部キーではないキーを更新してみる
sqlite> update child_cascade_u set c_col3='aa_school' where c_col1=1;
sqlite> select * from child_cascade_u;
c_col1 cr_col2 c_col3
------ ------- ---------
1 100 aa_school -- 更新されている
sqlite> select * from grand_child_cascade_u;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
second_exami2020 100 200 -- 10 から 100 に更新されている
---- grand_child_cascade_uテーブルの外部キーを更新してみる
sqlite> update grand_child_cascade_u set gcr_col2=1000 where gcr_col2=100;
Error: FOREIGN KEY constraint failed
-- 外部キーの変更は親キーに伝播できない
---- grand_child_cascade_uの外部キーではないキーを更新してみる
sqlite> update grand_child_cascade_u set gc_col3=20 where gc_col3=200;
sqlite> select * from grand_child_cascade_u;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
second_exami2020 100 20 -- 外部キーになっていないキーは更新できる親キーの削除に連動して子キーをNULLに更新する on delete set null
child の外部キーに on delete set null を加えた、child_setnull_d、parent3を定義して動作を確認してみます。
---- parent3、child_setnull_dテーブルを定義
sqlite> create table parent3 (
...> p_col1 integer primary key not null,
...> p_col2 text unique not null,
...> p_col3 text
...> );
sqlite> create table child_setnull_d (
...> c_col1 integer primary key not null,
...> cr_col2 integer unique
...> references parent3 on delete set null, -- not null制約ではない
...> c_col3 text
...> );
---- データを挿入してみる
sqlite> insert into parent3 values(null, 'takai', 'pecker@unknown.com');
sqlite> insert into parent3 values(null, 'arai', 'pecker2@unknown.com');
sqlite> select * from parent3;
p_col1 p_col2 p_col3
------ ------ ------------------
1 takai pecker@unknown.com
2 arai pecker2@unknown.com
sqlite> insert into child_setnull_d values(null, 1, 'a_school');
sqlite> select * from child_setnull_d;
c_col1 cr_col2 c_col3
------ ------- --------
1 1 a_school
---- 親キーを削除してみる
sqlite> delete from parent3 where p_col1=1;
sqlite> select * from parent3;
p_col1 p_col2 p_col3
------ ------ -------------------
2 arai pecker2@unknown.com
---- 子キーを含むレコードを表示してみる
sqlite> select * from child_setnull_d;
c_col1 cr_col2 c_col3
------ ------- --------
1 a_school -- null に更新されている
---- grand_childテーブル親キー に伝播させたgrand_child_cascade_u2 テーブルを定義する
sqlite> create table grand_child_cascade_u2 (
...> gc_col1 text not null,
...> gcr_col2 integer, -- not null制約を外す
...> gc_col3 integer not null,
...> primary key (gc_col1, gcr_col2),
...> foreign key (gcr_col2)
...> references child_setnull_d(cr_col2) on update cascade -- on delete set nullではない
...> );
---- データを追加する
sqlite> insert into parent3 values(null, 'kimura', 'pecker3@unknown.com');
sqlite> insert into parent3 values(null, 'wada', 'pecker4@unknown.com');
sqlite> insert into parent3 values(null, 'endou', 'pecker5@unknown.com');
sqlite> select * from parent3;
p_col1 p_col2 p_col3
------ ------ -------------------
2 arai pecker2@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
sqlite> insert into child_setnull_d values(null, 3, 'b_school');
sqlite> insert into child_setnull_d values(null, 4, 'c_school');
sqlite> insert into child_setnull_d values(null, 5, 'd_school');
sqlite> select * from child_setnull_d;
c_col1 cr_col2 c_col3
------ ------- --------
1 a_school
2 3 b_school
3 4 c_school
4 5 d_school
sqlite> insert into grand_child_cascade_u2 values('first_exami2020', 3, 250);
sqlite> insert into grand_child_cascade_u2 values('second_exami2020', 4, 210);
sqlite> insert into grand_child_cascade_u2 values('last_exami2020', 3, 210);
sqlite> insert into grand_child_cascade_u2 values('last_exami2020', 5, 180);
sqlite> insert into grand_child_cascade_u2 values('first_exami2021', 5, 270);
sqlite> select * from grand_child_cascade_u2;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
first_exami2020 3 250
second_exami2020 4 210
last_exami2020 3 210
last_exami2020 5 180
first_exami2021 5 270
---- grand_child_cascade_u2 の親キーの親キーであるparent3を確認する
sqlite> select * from parent3;
p_col1 p_col2 p_col3
------ ------ -------------------
2 arai pecker2@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
---- parent3 の親キーとなるレコードを削除してみる
sqlite> delete from parent3 where p_col1=3;
sqlite> select * from child_setnull_d;
c_col1 cr_col2 c_col3
------ ------- --------
1 a_school
2 b_school -- null にセットされている
3 4 c_school
4 5 d_school
sqlite> select * from grand_child_cascade_u2;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
first_exami2020 250 -- null にセットされている
second_exami2020 4 210
last_exami2020 210 -- null にセットされている
last_exami2020 5 180
first_exami2021 5 270
---- ggrand_child テーブルの親キーに伝播させたggrand_child_cascade_u2 を定義する
sqlite> create table ggrand_child_cascade_u2 (
...> ggcr_col1 text,
...> ggcr_col2 integer,
...> ggc_col3 text not null,
...> ggc_col4 integer not null,
...> primary key (ggcr_col1, ggcr_col2),
...> foreign key (ggcr_col1, ggcr_col2)
...> references grand_child_cascade_u2(gc_col1, gcr_col2) on update cascade
...> );
---- データを挿入する
sqlite> insert into ggrand_child_cascade_u2 values('last_exami2020', 5, 'mathematics', 90);
sqlite> insert into ggrand_child_cascade_u2 values('second_exami2020', 4, 'english', 60); sqlite> select * from ggrand_child_cascade_u2;
ggcr_col1 ggcr_col2 ggc_col3 ggc_col4
---------------- --------- ----------- --------
last_exami2020 5 mathematics 90
second_exami2020 4 english 60
---- parent3 の親キーとなるレコードを削除してみる
sqlite> delete from parent3 where p_col1=5;
---- 子キーが null にセットされているか確認してみる
sqlite> select * from ggrand_child_cascade_u2;
ggcr_col1 ggcr_col2 ggc_col3 ggc_col4
---------------- --------- ----------- --------
last_exami2020 mathematics 90 -- null にセットされている
second_exami2020 4 english 60
sqlite> select * from grand_child_cascade_u2;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
first_exami2020 250
second_exami2020 4 210
last_exami2020 210
last_exami2020 180 -- null にセットされている
first_exami2021 270 -- null にセットされている
sqlite> select * from child_setnull_d;
c_col1 cr_col2 c_col3
------ ------- --------
1 a_school
2 b_school
3 4 c_school
4 d_school -- null にセットされている
---- grand_child_cascade_u2 の親キーとなるchild_setnull_dのレコードを削除してみる
sqlite> delete from child_setnull_d where cr_col2=4;
Error: FOREIGN KEY constraint failed
-- grand_child_cascade_u2にon delete制約が設定されていないので外部キー制約でブロックされる
-- ggrand_child_cascade_u2も同様であるテーブル名の変更は可能 [ALTER RENAME]
ALTER TABLE table_name RENAME TO new_table_name;sqlite> alter table parent_base rename to parent_renamed;
sqlite> .schema parent_renamed
CREATE TABLE IF NOT EXISTS "parent_renamed" (
p_col1 integer primary key not null,
p_col2 text unique not null,
p_col3 text
);外部キーとなる列の追加は可能 [ALTER ADD]
ALTER TABLE table_name ADD COLUMN column_def...;---- c_col4 as resent meeted student of my friend.
sqlite> alter table child_base add c_col4 text references parent_renamed(p_col2);
---- 親キーとなるレコードを追加する
sqlite> insert into parent_renamed values(null, 'tanaka', 'pecker6@unknown.com');
sqlite> select * from parent_renamed ;
p_col1 p_col2 p_col3
------ ------ -------------------
1 takai pecker@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
6 tanaka pecker6@unknown.com
---- 外部キー制約のある列追加定義後のレコードを追加する
sqlite> insert into child_base values(null, 6, 'f_scholl', 'takai');
sqlite> select * from child_base ;
c_col1 cr_col2 c_col3 c_col4
------ ------- -------- ------
1 1 a_school
2 3 b_school
3 4 c_school
4 5 d_school
5 6 f_scholl takai
---- 外部キー制約が機能しているか確認する
sqlite> delete from parent_renamed where p_col2='tanaka';
Error: FOREIGN KEY constraint failed
-- 外部キー制約を伴った列を追加することが出来る外部キーに on update と on delete を組み合わせた完成型
完成型のおける、外部キー制約の内容
- 子テーブルの外部キーが親テーブルの親キーに依存しています。
- 親テーブルから子テーブルに向かって外部キーで連結しており、階層構造になっています。
- 親テーブルの親キーデータが変更されると、子キーも連動して更新される。
- 親テーブル(parent)が削除されるとchild以下の子キーにnullが上書きされる。
- 親テーブル(child)が削除されるとgrand_child以下の子キーにnullが上書きされる。
- 親テーブル(grand_child)が削除されるとggrand_childの子キーに’null’が上書きされる。
---- テーブルを定義する
sqlite> create table parent (
...> p_col1 integer primary key not null,
...> p_col2 text unique not null,
...> p_col3 text
...> );
sqlite> create table child (
...> c_col1 integer primary key not null,
...> cr_col2 integer unique
...> references parent on delete set null on update cascade,
...> c_col3 text
...> );
sqlite> create table grand_child (
...> gc_col1 text not null,
...> gcr_col2 integer,
...> gc_col3 integer not null,
...> primary key (gc_col1, gcr_col2),
...> foreign key (gcr_col2)
...> references child(cr_col2) on delete set null on update cascade
...> );
sqlite> create table ggrand_child (
...> ggcr_col1 text default 'erased',
...> ggcr_col2 integer,
...> ggc_col3 text not null,
...> ggc_col4 integer not null,
...> primary key (ggcr_col1, ggcr_col2),
...> foreign key (ggcr_col1, ggcr_col2)
...> references grand_child(gc_col1, gcr_col2)
...> on delete set default on update cascade
...> );
sqlite> create index childindex on child(cr_col2);
---- parentにデータを挿入してみる
sqlite> insert into parent values(null, 'takai', 'pecker@unknown.com');
sqlite> insert into parent values(null, 'arai', 'pecker2@unknown.com');
sqlite> insert into parent values(null, 'kimura', 'pecker3@unknown.com');
sqlite> insert into parent values(null, 'wada', 'pecker4@unknown.com');
sqlite> insert into parent values(null, 'endou', 'pecker5@unknown.com');
sqlite> select * from parent;
p_col1 p_col2 p_col3
------ ------ -------------------
1 takai pecker@unknown.com
2 arai pecker2@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
---- childにデータを挿入してみる
sqlite> insert into child values(null, 1, 'a_school');
sqlite> insert into child values(null, 3, 'b_school');
sqlite> insert into child values(null, 4, 'c_school');
sqlite> insert into child values(null, 5, 'd_school');
sqlite> insert into child values(null, 6, 'e_school');
Error: FOREIGN KEY constraint failed
sqlite> insert into parent values(null, 'check1', 'check1@unknown.com');
sqlite> select * from parent;
p_col1 p_col2 p_col3
------ ------ -------------------
1 takai pecker@unknown.com
2 arai pecker2@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
6 check1 check1@unknown.com
sqlite> insert into child values(null, 6, 'for_check');
sqlite> select * from child;
c_col1 cr_col2 c_col3
------ ------- ------------
1 1 a_school
2 3 b_school
3 4 c_school
4 5 d_school
5 6 for_check
sqlite> update parent set p_col1=60 where p_col1=6;
sqlite> select * from parent;
p_col1 p_col2 p_col3
------ ------ -------------------
1 takai pecker@unknown.com
2 arai pecker2@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
60 check1 check1@unknown.com
sqlite> select * from child;
c_col1 cr_col2 c_col3
------ ------- ------------
1 1 a_school
2 3 b_school
3 4 c_school
4 5 d_school
5 60 for_check
sqlite> delete from parent where p_col1=60;
sqlite> select * from parent;
p_col1 p_col2 p_col3
------ --------- -------------------
1 takai pecker@unknown.com
2 arai pecker2@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
sqlite> select * from child;
c_col1 cr_col2 c_col3
------ ------- ------------
1 1 a_school
2 3 b_school
3 4 c_school
4 5 d_school
5 for_check
sqlite> insert into grand_child values('second_exami2020', 5, 200);
sqlite> insert into grand_child values('second_exami2020', 2, 200);
Error: FOREIGN KEY constraint failed
sqlite> insert into grand_child values('second_exami2020', 1, 280);
sqlite> insert into grand_child values('last_exami2020', 1, 190);
sqlite> insert into grand_child values('last_exami2020', 1, 100);
Error: UNIQUE constraint failed: grand_child.gc_col1, grand_child.gcr_col2
sqlite> insert into grand_child values('last_exami2020', 4, 100);
sqlite> select * from grand_child;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
second_exami2020 5 200
second_exami2020 1 280
last_exami2020 1 190
last_exami2020 4 100
---- parent 更新チェック
sqlite> insert into parent values(null, 'tanaka', 'pecker6@unknown.com');
sqlite> select * from parent;
p_col1 p_col2 p_col3
------ ------ -------------------
1 takai pecker@unknown.com
2 arai pecker2@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
6 tanaka pecker6@unknown.com
sqlite> update child set cr_col2=6 where c_col1=5;
sqlite> select * from child;
c_col1 cr_col2 c_col3
------ ------- ---------
1 1 a_school
2 3 b_school
3 4 c_school
4 5 d_school
5 6 for_check
sqlite> update parent set p_col1=60 where p_col1=6;
sqlite> select * from parent;
p_col1 p_col2 p_col3
------ ------ -------------------
1 takai pecker@unknown.com
2 arai pecker2@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
60 tanaka pecker6@unknown.com -- update
sqlite> select * from child;
c_col1 cr_col2 c_col3
------ ------- ---------
1 1 a_school
2 3 b_school
3 4 c_school
4 5 d_school
5 60 for_check -- update
sqlite> select * from grand_child;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
second_exami2020 5 200
second_exami2020 1 280
last_exami2020 1 190
last_exami2020 4 100
for_check2 60 999 -- update
---- parent 削除チェック
sqlite> delete from parent where p_col1=60;
sqlite> select * from parent;
p_col1 p_col2 p_col3
------ ------ -------------------
1 takai pecker@unknown.com
2 arai pecker2@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
sqlite> select * from child;
c_col1 cr_col2 c_col3
------ ------- ---------
1 1 a_school
2 3 b_school
3 4 c_school
4 5 d_school
5 for_check -- on delete set null
sqlite> select * from grand_child;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
second_exami2020 5 200
second_exami2020 1 280
last_exami2020 1 190
last_exami2020 4 100
for_check2 999 -- on delete set null
---- child 更新・削除チェック
-- 親キーがnullの場合のupdate
sqlite> update child set cr_col2=2 where c_col1=5;
sqlite> select * from child;
c_col1 cr_col2 c_col3
------ ------- ---------
1 1 a_school
2 3 b_school
3 4 c_school
4 5 d_school
5 2 for_check
sqlite> select * from grand_child;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
second_exami2020 5 200
second_exami2020 1 280
last_exami2020 1 190
last_exami2020 4 100
for_check2 999 -- nullに更新されてものは on updateのアクションがおきない
---- child の親キーを削除してみる
sqlite> delete from child where cr_col2=4;
sqlite> select * from child;
c_col1 cr_col2 c_col3
------ ------- ---------
1 1 a_school
2 3 b_school
4 5 d_school
5 2 for_check
sqlite> select * from grand_child;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
second_exami2020 5 200
second_exami2020 1 280
last_exami2020 1 190
last_exami2020 100 -- on delete set null適用される
for_check2 999
---- child の親キーを更新してみる
sqlite> update child set cr_col2=5 where cr_col2=4;
sqlite> select * from child;
c_col1 cr_col2 c_col3
------ ------- ---------
1 1 a_school
2 3 b_school
4 5 d_school -- 親キーを更新
5 2 for_check
sqlite> select * from grand_child;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
second_exami2020 5 200 -- 子キーが更新される
second_exami2020 1 280
last_exami2020 1 190
last_exami2020 100
for_check2 999
---- ggrand_child の外部キー制約を確認してみる
-- データを挿入する
sqlite> insert into ggrand_child values('second_exami2020', 1, 'mathematics', 90);
-- 親キーの一部 gcr_col2 を更新してみる
sqlite> update grand_child set gcr_col2=3 where gc_col1='second_exami2020' and gcr_col2=1;
sqlite> select * from grand_child;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
second_exami2020 5 200
second_exami2020 3 280 -- 更新している
last_exami2020 1 190
last_exami2020 100
for_check2 999
sqlite> select * from ggrand_child;
ggcr_col1 ggcr_col2 ggc_col3 ggc_col4
---------------- --------- ----------- --------
second_exami2020 3 mathematics 90 -- 連動して更新されている
-- 親キーの一部 gc_col1 を更新してみる
sqlite> update grand_child set gc_col1='second_exami2022' where gc_col1='second_exami2020' and gcr_col2=3;
sqlite> select * from grand_child;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
second_exami2020 5 200
second_exami2022 3 280 -- 更新されている
last_exami2020 1 190
last_exami2020 100
for_check2 999
sqlite> select * from ggrand_child;
ggcr_col1 ggcr_col2 ggc_col3 ggc_col4
---------------- --------- ----------- --------
second_exami2022 3 mathematics 90 -- 連動して更新されている
-- 親キー（複合キー）まるごと更新してみる
sqlite> update grand_child set gc_col1='last_exami2022', gcr_col2=2 where gc_col1='second_exami2022' and gcr_col2=3;
sqlite> select * from grand_child;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
second_exami2020 5 200
last_exami2022 2 280 -- 更新されている
last_exami2020 1 190
last_exami2020 100
for_check2 999
sqlite> select * from ggrand_child;
ggcr_col1 ggcr_col2 ggc_col3 ggc_col4
-------------- --------- ----------- --------
last_exami2022 2 mathematics 90 -- 連動して更新されている
---- 削除についてみてみる
sqlite> select * from grand_child;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
second_exami2020 5 200
last_exami2022 2 280
last_exami2020 1 190
last_exami2020 100
for_check2 999
-- 親キーを削除する
sqlite> delete from grand_child where gc_col1='last_exami2022' and gcr_col2=2;
sqlite> select * from grand_child;
gc_col1 gcr_col2 gc_col3
---------------- -------- -------
second_exami2020 5 200
last_exami2020 1 190
last_exami2020 100
for_check2 999
sqlite> select * from ggrand_child;
ggcr_col1 ggcr_col2 ggc_col3 ggc_col4
--------- --------- ----------- --------
erased mathematics 90
-- ggcr_col1にdefault値がセットされている。セットされていないggcr_col2はnullがセットされる
---- 親キーかつ複合キーである場合、親キー削除後の複合キーがダブってしまうときの動作確認
sqlite> insert into ggrand_child values('second_exami2020', 5, 'english', 75);
sqlite> select * from ggrand_child;
ggcr_col1 ggcr_col2 ggc_col3 ggc_col4
---------------- --------- ----------- --------
erased mathematics 90
second_exami2020 5 english 75
sqlite> delete from grand_child where gc_col1='second_exami2020' and gcr_col2=5;
sqlite> select * from grand_child;
gc_col1 gcr_col2 gc_col3
-------------- -------- -------
last_exami2020 1 190
last_exami2020 100
for_check2 999
sqlite> select * from ggrand_child;
ggcr_col1 ggcr_col2 ggc_col3 ggc_col4
--------- --------- ----------- --------
erased mathematics 90
erased english 75 -- 複合キーがプライマリーキー制約にならない
外部キーで連結している場合、on update on delete を定義することは、テーブル間の連動を十分意識する必要があります。
遅延外部キー制約 Deferred Foreign Key Constraints [DEFERRABLE INITIALLY IMMEDIATE]
外部キーはデフォルトで「即時」に設定されており、制約に違反するステートメントは実行が完了するとすぐにエラーがトリガーされます。遅延外部キー は、COMMIT が実行されるまでエラーをトリガーしません。エラーが解消されない限りCOMMIT できないことになります。
CREATE TABLE ,,,,, REFERENCES DEFERRABLE INITIALLY IMMEDIATE---- サンプルとなるparent4テーブルを定義する
sqlite> create table parent4(
...> p_col1 integer primary key not null,
...> p_col2 text unique not null,
...> p_col3 text
...> );
---- 遅延外部キー制約である外部キーを含むchildテーブルを定義する
sqlite> create table child_defer (
...> c_col1 integer primary key not null,
...> cr_col2 integer not null unique
...> references parent4 deferrable initially deferred,
...> c_col3 text
...> );
---- 親テーブルにデータを入力する
sqlite> insert into parent4 values(null, 'takai', 'pecker@unknown.com');
sqlite> insert into parent4 values(null, 'arai', 'pecker2@unknown.com');
sqlite> insert into parent4 values(null, 'kimura', 'pecker3@unknown.com');
sqlite> insert into parent4 values(null, 'wada', 'pecker4@unknown.com');
sqlite> insert into parent4 values(null, 'endou', 'pecker5@unknown.com');
sqlite> select * from parent4;
p_col1 p_col2 p_col3
------ ------ -------------------
1 takai pecker@unknown.com
2 arai pecker2@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
---- 子テーブルにデータを入力する
sqlite> insert into child_defer values(null, 1, 'a_school');
sqlite> insert into child_defer values(null, 6, 'e_school');
Error: FOREIGN KEY constraint failed -- 即時外部キー制約による
sqlite> begin;
sqlite> insert into child_defer values(null, 3, 'b_school');
sqlite> insert into child_defer values(null, 6, 'e_school'); -- 外部キー制約エラー表示なし
sqlite> select * from child_defer;
c_col1 cr_col2 c_col3
------ ------- --------
1 1 a_school
2 3 b_school
3 6 e_school -- トランザクション内では表示される
sqlite> commit;
Error: FOREIGN KEY constraint failed -- エラーが表示される
sqlite> begin;
Error: cannot start a transaction within a transaction -- トランザクション継続中
---- エラーとなっている外部キーの親キーを挿入する
sqlite> insert into parent4 values(null, 'tanaka', 'pecker6@unknown.com');
sqlite> select * from parent4;
p_col1 p_col2 p_col3
------ ------ -------------------
1 takai pecker@unknown.com
2 arai pecker2@unknown.com
3 kimura pecker3@unknown.com
4 wada pecker4@unknown.com
5 endou pecker5@unknown.com
6 tanaka pecker6@unknown.com
sqlite> commit; -- 外部キー制約が解除された
sqlite> select * from child_defer;
c_col1 cr_col2 c_col3
------ ------- --------
1 1 a_school
2 3 b_school
3 6 e_school -- データが追加された
create ステートメント定義以外にも、遅延外部キー制約をpragmaの記述により、設定することが出来ます。
pragma defer_foreign_keys = ON;
すべての外部キー制約を一時的に deferred に変更
