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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

実際の実装      PostgreSQLの場合

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

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

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

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

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

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

ただし、遅延制約を有効にしていても、対象のテーブルに設定されているCASCADEのトリガは遅延しない  PostgreSQLの外部キー制約の実装  あとから遅延制約に変更できないので作り直しになる   RoRとかDjangoは最初から遅延制約を設定し、   遅延制約を無効の状態で外部キー制約を作成する  

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

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

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

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

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

• PostgreSQLのCREATE TABLEにはMATCH句がある   ◦ 複合外部キー制約のときの振る舞いを指定できる   • MATCH SIMPLE（デフォルト） 
◦ 複合外部キー制約の一部がNULLになることを許可し、対象の行は制約のチェック対象外にする  ◦ MySQLと一緒  • MATCH FULL  ◦ 複合外部キー制約は全部NULLか、全部設定するか強制する   • MATCH PARTIALは未実装  ◦ 外部キー制約の一部のNULLを許可するが、NULL以外の列の組み合わせは   親テーブルと一致している必要がある   PostgreSQLの外部キー制約の実装  SQLアンチパターン第二版の追加の章として出てくる   みんなMATCH句を知らないって嘆いてた  

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

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

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

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

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 );

外部キー制約が救ってくれること 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

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

外部キー制約が救ってくれること 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店舗の注文にしたい場合は？  

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

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

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

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に紐づく  

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しか登録できない  

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) ); itemに紐づいたshopしか登録できない  

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) ); idだけでユニークだが、外部キー制約はユニークキーしか登録できないのでshop_idと組み合わせる  これでshop_order_detailに登録されるときに orderとitemのshopは必ず一致する   これによってorderに紐づいていないshopのitemは誤登録されない  

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

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

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

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

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

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

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

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

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

      おわりに Simple is Beautiful     

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

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

      おわりに

昨日の自分に誇れる    今日の自分になろう  おわりに

ご清聴ありがとうございました      おわりに

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

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

Transcript

More Decks by soudai sone

Other Decks in Technology

Featured