外部キー制約の知っておいて欲しいこと - RDBMSを正しく使うために必要なこと / FOREIGN KEY Night

by soudai sone

Slide 1

Slide 1 text

外部キーNight 2026   外部キー制約の知っておいて欲しいこと  - RDBMSを正しく使うために必要なこと 

Slide 2

Slide 2 text

データモデリングがなぜ重要か      What is it? 

Slide 3

Slide 3 text

リレーショナルデータベースは    集合と関係の扱うデータモデル  What is it? 

Slide 4

Slide 4 text

関係は意図的な整合性を含んだ集合      What is it? 

Slide 5

Slide 5 text

関係は意図的な整合性を含んだ集合  ↓  外部キー制約がその意図を表現する  What is it? 

Slide 6

Slide 6 text

外部キー制約が無いなら    「RDBを使っている」とは言えない  What is it? 

Slide 7

Slide 7 text

外部キー制約が無いなら    「RDBを使っている」とは言えない  What is it?  それはKeyValueStoreですよ  

Slide 8

Slide 8 text

正しくRDBMSを活用するために必要な    外部キー制約の使い方をお話します  What is it? 

Slide 9

Slide 9 text

1. 自己紹介  2. 外部キー制約はどのように動くのか  3. 外部キー制約が救ってくれること  4. おわりに  あじぇんだ 

Slide 10

Slide 10 text

1. 自己紹介  2. 外部キー制約はどのように動くのか  3. 外部キー制約が救ってくれること  4. おわりに  あじぇんだ 

Slide 11

Slide 11 text

自己紹介  曽根壮大（41歳）  Have Fun Tech LLC 代表社員  株式会社リンケージ CTO 兼 COO    そ  ● 日本PostgreSQLユーザ会勉強会分科会担当  ● 3人の子供がいます（長女、次女、長男）  ● 技術的にはWeb/LL言語/RDBMSが好きです  ● コミュニティが好きたけ  ね  とも 

Slide 12

Slide 12 text

No content

Slide 13

Slide 13 text

1. 自己紹介  2. 外部キー制約はどのように動くのか  3. 外部キー制約が救ってくれること  4. おわりに  あじぇんだ 

Slide 14

Slide 14 text

外部キー制約の動き        外部キー制約はどのように動くか

Slide 15

Slide 15 text

外部キー制約の動き  ↓  　キーになる参照元（子）の値は  参照先（親）に必ず存在することを担保する  外部キー制約はどのように動くか

Slide 16

Slide 16 text

外部キー制約はどのように動くか親  子  A  A 

Slide 17

Slide 17 text

外部キー制約はどのように動くか親  子  A  A  外部キー制約の対象は必ず親に対象の値がある  

Slide 18

Slide 18 text

外部キー制約はどのように動くか親  子  A  A  B  親にない値は作成できない  

Slide 19

Slide 19 text

外部キー制約はどのように動くか親  子  A  A  子が参照している値を消すと不整合が発生する  

Slide 20

Slide 20 text

親の値が変更されたときに    整合性を保つ処理  外部キー制約はどのように動くか

Slide 21

Slide 21 text

● RESTRICT / NO ACTION  ○ 子がいる限り、親を消せない  ● CASCADE  ○ 親を消すと子も消える  ○ 更新の場合は同様の値に変更を追従する  ● SET NULL  ○ 親が消えると子のFKをNULLにする  ● SET DEFAULT  ○ 親が消えると子のFKをDEFAULTにする  ○ DEFAULT値に対応する親に存在しないと失敗する  外部キー制約はどのように動くか

Slide 22

Slide 22 text

実際の実装    ※InnoDBの話です  MySQL(InnoDB）の場合

Slide 23

Slide 23 text

MySQLの場合親  子  A  A  index  B  値を確認するときはインデックスを確認する  

Slide 24

Slide 24 text

MySQLの場合  親  子  A  A  index  親の値を変更するときは   対象のテーブルの子のインデックスを確認する   子  index  A  A  子  index 

Slide 25

Slide 25 text

● MySQLは参照先のインデックスを確認して、存在確認する  ○ そのため子にも対象にインデックスがない場合は  自動的にインデックスを作成する  ● MySQL8.4未満はDDL上では親テーブルのKeyがユニークでなくてもテーブルを作れる  ○ データ登録や更新の時にさすがにエラーが出る  ○ 8.4からはそもそもテーブルも作れなくなった  ○ restrict_fk_on_non_standard_key=onになってる  MySQLの外部キー制約の実装 

Slide 26

Slide 26 text

● MySQLには遅延制約がないため、実行時に即チェック  ○ 遅延制約がないのでRESTRICTとNO ACTIONは実質同様  ○ foreign_key_checks=0で制約無効にできるけど……  ● 文字列の場合はcharsetとcollationも一致する必要がある  ○ ちなみに外部キー制約が無くてもJOINのときにINDEXを使ってくれないので外部キー制約を使っていなくても注意  ● InnoDBはSET DEFAULTを設定しようとするとREJECTされるので実質MySQLではSET DEFAULTは使えない  MySQLの外部キー制約の実装 

Slide 27

Slide 27 text

● MySQL(InnoDB)にはMATCH句が存在しない  ○ DDLに書けるし、実行できるがスルーされる  ○ 昔のCHECK制約と同じ挙動  ○ 実際にはMATCH SIMPLE相当  ■ PostgreSQLのときに説明します  ● 指定するとON DELETE/ON UPDATEが無視される  ○ 指定は非推奨とされている（公式ドキュメント記載）  MySQLの外部キー制約の実装 

Slide 28

Slide 28 text

● 外部キーは行単位でチェックされる  ○ INDEXがあるので行レベルの参照ロックを取る  ● CASCADEではTrigger Eventを発行しない  ○ つまりCASCADEで変更時にトリガが起動しない  MySQLの外部キー制約の実装 

Slide 29

Slide 29 text

実際の実装      PostgreSQLの場合

Slide 30

Slide 30 text

PostgreSQLの場合親  子  A  A  値を確認するときは実テーブルを確認する   B 

Slide 31

Slide 31 text

PostgreSQLの場合  親  子  A  A  親の値を変更するときは   対象の子テーブルを参照する   子  A  子  A 

Slide 32

Slide 32 text

● PostgreSQLは対象のイベント発生時にトリガが起動して  対象のテーブルにSQLを実行する  ○ 子テーブルにはINDEXは自動的に設定されない  ○ なので、親テーブルのDELETEのときにINDEXがない場合はテーブルスキャンが実行される  ○ パフォーマンスのボトルネックになりがち  ■ そもそも親テーブルをDELETEするなという話もある  ○ もちろんINDEXを作成すればINDEXスキャンになる  PostgreSQLの外部キー制約の実装 

Slide 33

Slide 33 text

Slide 34

Slide 34 text

Slide 35

Slide 35 text

● PostgreSQLはデフォルトは実行時に即チェック  ○ PostgreSQLには遅延制約がある  ○ 外部キー制約を作成時に遅延制約（DEFERRABLE）を設定すれば、遅延制約を利用できる状態になる  ● 遅延制約を有効にすればCOMMIT時に制約チェックをする  ○ ただし、遅延制約を有効にしていても、対象のテーブルに設定されているCASCADEのトリガは遅延しない  PostgreSQLの外部キー制約の実装  MySQLとの違いにPostgreSQLはCASCADEで実行されたクエリに対してもTrigger Eventを発行する   子テーブルが消されたarchiveテーブルに保存するトリガなどを仕掛けることができる   暗黙的に動くのでもちろん非推奨だがデバッグやトラブルシューティングで必要になることがある  

Slide 36

Slide 36 text

● PostgreSQLはデフォルトは実行時に即チェック  ○ PostgreSQLには遅延制約がある  ○ 外部キー制約を作成時に遅延制約（DEFERRABLE）を設定すれば、遅延制約を利用できる状態になる  ● 遅延制約を有効にすればCOMMIT時に制約チェックをする  ○ ただし、遅延制約を有効にしていても、対象のテーブルに設定されているCASCADEのトリガは遅延しない  PostgreSQLの外部キー制約の実装  あとから遅延制約に変更できないので作り直しになる   RoRとかDjangoは最初から遅延制約を設定し、   遅延制約を無効の状態で外部キー制約を作成する  

Slide 37

Slide 37 text

PostgreSQLの場合  データ登録  遅延制約  データ削除  COMMIT  チェック  Transaction  データ登録  チェック  即時チェック  データ登録  チェック  COMMIT  Transaction  データ登録 

Slide 38

Slide 38 text

PostgreSQLの場合  データ登録  遅延制約  データ削除  COMMIT  チェック  Transaction  データ登録  チェック  即時チェック  データ登録  チェック  COMMIT  Transaction  データ登録  CASCADE  トリガー  チェック 

Slide 39

Slide 39 text

● PostgreSQLの参照アクション  ○ SET DEFAULTが使える  ■ 削除時に対象の子テーブルにDEFAULT値をセット  ■ ただし、DEFAULT値が親テーブルに存在すること  ○ 親テーブルの削除や更新の時に利用されてる値があれば RESTRICTは遅延制約が有効時でも即チェック  ○ NO ACTIONは遅延制約が有効時はCOMMIT時に  子テーブルをチェックし、利用されていればエラー  PostgreSQLの外部キー制約の実装 

Slide 40

Slide 40 text

Slide 41

Slide 41 text

● PostgreSQLのCREATE TABLEにはMATCH句がある   ○ 複合外部キー制約のときの振る舞いを指定できる   ● MATCH SIMPLE（デフォルト）  ○ 複合外部キー制約の一部がNULLになることを許可し、対象の行は制約のチェック対象外にする  ○ MySQLと一緒  ● MATCH FULL  ○ 複合外部キー制約は全部NULLか、全部設定するか強制する   ● MATCH PARTIALは未実装  ○ 外部キー制約の一部のNULLを許可するが、NULL以外の列の組み合わせは   親テーブルと一致している必要がある   PostgreSQLの外部キー制約の実装 

Slide 42

Slide 42 text

Slide 43

Slide 43 text

1. 自己紹介  2. 外部キー制約はどのように動くのか  3. 外部キー制約が救ってくれること  4. おわりに  あじぇんだ 

Slide 44

Slide 44 text

外部キー制約がないとどうなるか？      外部キー制約が救ってくれること

Slide 45

Slide 45 text

外部キー制約がないとどうなるか？  ↓  データが壊れる  外部キー制約が救ってくれること

Slide 46

Slide 46 text

● 親テーブルに無い値や組み合わせを  子テーブルに登録できる  ● 子テーブルが使っている値や組み合わせを  親テーブルから削除できる  ● 紐づいてはいけないデータが紐づいてしまう  外部キー制約が無いとデータが壊れる 

Slide 47

Slide 47 text

● 親テーブルに無い値や組み合わせを  子テーブルに登録できる  ● 子テーブルが使っている値や組み合わせを  親テーブルから削除できる  ● 紐づいてはいけないデータが紐づいてしまう  外部キー制約が無いとデータが壊れる  ここが軽視されがち 

Slide 48

Slide 48 text

create table shop ( id integer not null constraint shop_pk primary key, name integer );   外部キー制約が救ってくれること create table "order" ( id integer not null , item_id integer not null constraint order_item_id_fk references item, constraint order_pk primary key (id, item_id) ); create table item ( id integer not null constraint item_pk primary key, shop_id integer constraint item_shop_id_fk references shop );

Slide 49

Slide 49 text

外部キー制約が救ってくれること id name 1 shop A 2 shop B id shop_id 1 1 2 1 3 2 id item_id 1 1 1 3 2 1

Slide 50

Slide 50 text

よく見るテーブルだが……？      外部キー制約が救ってくれること

Slide 51

Slide 51 text

外部キー制約が救ってくれること id name 1 shop A 2 shop B id shop_id 1 1 2 1 3 2 id item_id 1 1 1 3 2 1 注文が同時に複数の店舗に注文している   これを1回の注文では1店舗の注文にしたい場合は？  

Slide 52

Slide 52 text

？？？「アプリケーションで確認します」      外部キー制約が救ってくれること

Slide 53

Slide 53 text

？？？「アプリケーションで確認します」  ↓  そして始まる地獄のデータ不整合との戦い  外部キー制約が救ってくれること

Slide 54

Slide 54 text

外部キー制約で正しく守る      外部キー制約が救ってくれること

Slide 55

Slide 55 text

create table shop ( id integer not null constraint shop_pk primary key, name integer ); 外部キー制約が救ってくれること create table shop_order_detail ( order_id integer not null , shop_id integer not null , item_id integer not null , constraint shop_order_detail_pk primary key (order_id, shop_id, item_id), constraint shop_order_detail_shop_order_id_shop_id_fk foreign key (order_id, shop_id) references shop_order, constraint shop_order_detail_item_id_shop_id_fk foreign key (item_id, shop_id) references item (id, shop_id) ); create table item ( id integer not null constraint item_pk primary key, shop_id integer constraint item_shop_id_fk references shop, constraint item_pk_2 unique (id, shop_id) ); create table shop_order ( id integer not null , shop_id integer not null constraint shop_order_shop_id_fk references shop, constraint shop_order_pk primary key (id, shop_id) ); orderは必ずshopに紐づく  

Slide 56

Slide 56 text

Slide 57

Slide 57 text

Slide 58

Slide 58 text

Slide 59

Slide 59 text

外部キー制約が救ってくれること

Slide 60

Slide 60 text

外部キー制約はヒューマンエラーと    アプリケーションのバグから守ってくれる  外部キー制約が救ってくれること

Slide 61

Slide 61 text

1. 自己紹介  2. 外部キー制約はどのように動くのか  3. 外部キー制約が救ってくれること  4. おわりに  あじぇんだ 

Slide 62

Slide 62 text

外部キー制約はテーブルモデルの    リレーションシップそれのもの  おわりに

Slide 63

Slide 63 text

シンプルで堅牢なデータモデルは    アプリケーションの形を導出する  おわりに

Slide 64

Slide 64 text

シンプルで堅牢なデータモデルは    アプリケーションの形を導出する  おわりにつまり人間もAIもわかりやすく、間違えにくい  

Slide 65

Slide 65 text

データの寿命は    アプリケーションよりも長い  おわりに

Slide 66

Slide 66 text

アプリケーションは何度も作り直せても    データは作り直せない  おわりに

Slide 67

Slide 67 text

ビジネスに踏み込み    データモデリングを考え抜きましょう  おわりに

Slide 68

Slide 68 text

データモデリングはスキルなので    正しく学べば身につけることができる  おわりに

Slide 69

Slide 69 text

      おわりに Simple is Beautiful     

Slide 70

Slide 70 text

      おわりに Simple is Beautiful  ↓  常に追求した者だけが辿り着ける 

Slide 71

Slide 71 text

今、こだわり抜いた設計が    未来の自分を救うことになる  おわりに