DB過去問解説〜参照制約【平成30年問2】

DB

この記事では、データベーススペシャリスト試験の対策として、『参照制約』についての問題を扱った過去問の解説を紹介します。対象は、平成30年度午後1問2の問題となります。

問1（SQL問題のため解説なし）

設問2（1）

設問2（1）は、参照制約における制約検査での処理結果（正常終了／エラー）について回答する問題。

〔RDMSの主な仕様〕の「1．参照制約」に仕様が記載してある。
＜回答方式＞
・（a）、（b）の処理結果について、エラーの場合は理由も答える
＜条件＞
・（a）；新規従業員登録のために、所属未定（部署コード；NULL）の行を”従業員”テーブルに挿入
テーブル名；従業員の列名；部署コード、参照先；部署（部署コード）、実行契機；UPDATE、挙動モード；NO ACTION、検査契機モード；猶予モード（表3「参照制約機能の利用案」から）
・（b）；ある部署の管理職退職に伴い、”従業員”テーブルから当該従業員を削除
テーブル名；部署、列名；管理者従業員コード、参照先；従業員（従業員コード）、実行契機；DELETE、挙動モード；NO ACTION、検査契機モード；即時モード（表3「参照制約機能の利用案」から）

＜回答＞
（a）について、表3「参照制約機能の利用案」から、
・「テーブル名；従業員、列名；部署コード、参照先；部署（部署コード）、実行契機；UPDATE、挙動モード；NO ACTION、検査契機モード；猶予モード」であることが分かる。
参照制約における、参照先テーブルの外部キー追加時の処理のポイントは、参照元の外部キーとして設定されている場合かNULL（非NULL設定をしていない場合に限り）しか設定できない。
今回の場合は、非NULLが設定されていないので、外部キー追加時の処理ポイントを満たしている。
したがって、処理結果は『正常終了』となる。

参照先テーブル（部署）；問題なく実行可能
参照元テーブル（従業員）；挙動モードの確認が必要？→

（b）について、表3「参照制約機能の利用案」から、
「テーブル名；部署、列名；管理者従業員コード、参照先；従業員（従業員コード）、実行契機；DELETE、挙動モード；NO ACTION、検査契機モード；即時モード」であることが分かる。
参照制約における、参照先テーブルの外部キー削除時の処理のポイントは、参照元の外部キーとして設定されている場合（NULLでない場合）、削除できない。
テーブル仕様の挙動モード；NO ACTIONとなっており、参照元テーブルに対して何もしないこととなる。挙動モード；CASCADEの場合は、参照元テーブルも削除を連鎖させるので、参照元・参照先ともになくなり、参照制約のポイントに違反せず、処理は正常終了する。
したがって、今回の場合、処理結果は『エラー』となり、理由は、『”部署”テーブルの管理者従業員コードの参照制約に違反するから』や『参照先（従業員）テーブルの外部キー（従業員コード）を削除した結果、参照元テーブル（部署）に存在する外部キー（管理者従業員コード）に対しては出来ないという参照制約に違反するから』となる。。

外部キーの処理による違いのポイント


⬛︎外部キーの処理による違いのポイント；参照制約の基本事項
　・追加時；すでに存在するものかNULL
　・削除時；そのテーブルの外部キーが削除されるだけ

操作対象操作内容主な制約チェック挙動（エラー or 正常）備考
参照先（親）追加（INSERT）特に参照制約なし（他から参照される側）常に正常終了子テーブルから参照される可能性があるキーを追加
参照先（親）削除（DELETE）子テーブルに対応する外部キーが存在するか– 子に該当行がある → 制約違反（RESTRICT／NO ACTION）- CASCADE → 子も削除- SET NULL → 子の外部キーがNULLになる「ON DELETE」の指定で挙動が変化する
参照元（子）追加（INSERT）外部キーの値が参照先に存在するか、またはNULLか– 存在 or NULL → 正常- 存在しない → 制約違反もっとも典型的な参照整合性チェック
参照元（子）削除（DELETE）特に制約なし（外部キーを削除するだけ）常に正常終了親には影響しない（片方向の制約）


操作参照制約の基本ルール挙動モード: NO ACTIONの挙動挙動モード: CASCADEの挙動
追加 (INSERT/UPDATE)外部キーに設定する値は、参照先テーブルの主キーに存在するか、NULLを許容している場合はNULLでなければならない。参照先に存在しない値や、NULL不可の列へのNULL値の挿入・更新を拒否する。参照元への挿入・更新操作であり、参照先に対するアクションはないため、NO ACTIONと同様に基本ルールに違反すれば拒否する。
削除/更新 (DELETE/UPDATE)参照先テーブルの主キーは、他のテーブルの外部キーから参照されていない場合に限り削除・更新できる。（または、参照されている場合も連鎖的な処理を行う）参照元テーブルの外部キーによって参照されている場合、操作そのものをエラーで拒否する。参照先テーブルの主キーが削除・更新された場合、参照元テーブルの外部キーも連鎖的に削除・更新する。これにより参照制約の違反を防ぎ、操作は正常終了する。

設問2（2）

設問2（2）は、トリガと参照制約の関係による不具合の対策に関する問題である。
＜条件＞
・不具合発生トリガ；”従業員”テーブル及び”従業員家族”テーブルから退職した従業員の行を削除して別テーブルに保存
・”従業員”テーブルのトリガ定義を変更した上で、新たなトリガを定義
・トリガの仕様（〔RDBMSの主な仕様〕の「2．トリガ」参照）
・テーブル名；従業員家族、列名；従業員コード、参照先；従業員（従業員コード）、実行契機；DELETE、挙動モード；CASCADE、検査契機モード；即時モード（表3「参照制約機能の利用案」から）
＜現状のトリガ＞
・実行タイミング；”従業員”テーブルの削除の後
・トリガの処理；
　①削除した”従業員”テーブルの行を別テーブルに挿入
　②”従業員家族”テーブルの家族行を別テーブルに挿入し、その後削除
＜回答方式＞
新たなトリガについて下記を回答
・対象となるテーブルのテーブル名
・実行タイミング
・処理内容

＜回答＞
不具合内容について考察。
表3「参照制約機能の利用案」から、「テーブル名；従業員家族、列名；従業員コード、参照先；従業員（従業員コード）、実行契機；DELETE、挙動モード；CASCADE、検査契機モード；即時モード」となっており、現状のトリガの「”従業員”テーブルの削除の後」で、参照元の’従業員家族’テーブルの”従業員コード”がCASCADEで削除されることとなっている。
図1「主なテーブル構造」から”従業員家族”テーブルの主キーの一部は”従業員コード”であり、保持していた従業員家族の行が失われてしまう。そのため、トリガの処理「②”従業員家族”テーブルの家族行を別テーブルに挿入し、その後削除」が出来ず、これが不具合と考えられる。整理すると、現状のトリガの問題点としては、次になる。
□「実行タイミング；”従業員”テーブルの削除の後」（CASCADEにより、”従業員家族”テーブル対象行がなくなり、トリガ処理を実行できない）

「”従業員”テーブルのトリガ定義を変更した上」とあるので、どう変更するか考える。まず、対応が必要になるのは、「実行タイミング；”従業員”テーブルの削除の後」に関してである。このタイミングでは、以降の処理が実行できないので、変更する。
それが、新たに定義するトリガとなる。
「”従業員家族”テーブルの家族行を別テーブルに挿入し、その後削除」出来ないことが問題となっている。「”従業員家族”テーブルの家族行を別テーブルに挿入」した後、削除すれば他の制約に引っかかることなく終了できる。したがって、トリガの対象テーブル『従業員家族』を実行タイミング『削除した後』とし、処理内容を『別テーブルに挿入する』とする。

ポイント

  • 参照制約（CASCADE） は参照元の行を自動削除してしまう。
  • そのため「親表削除後のトリガ」では参照元のデータが残っておらず、後続処理ができないことがある。
  • 対策：
    • トリガを「参照元テーブル（従業員家族）」側に定義する
    • タイミングを「削除後（AFTER DELETE）」にする
    • この順序なら、削除される直前の行データ（:OLD 値）を使って別テーブルへ退避できる

設問3（1）

設問3（1）は、更新手順の設計ミスによる不具合の内容の説明問題である。
＜条件＞
・図3「参照制約機能を利用する以前の更新手順」参照
・テーブル名；従業員の列名；部署コード、参照先；部署（部署コード）、実行契機；DELETE、挙動モード；CASCADE、検査契機モード；猶予モード（表3「参照制約機能の利用案」参照）

＜回答＞
表3「参照制約機能の利用案」を参照すると、「テーブル名；従業員｜列名；部署コード、参照先；部署（部署コード）、実行契機；UPDATE、挙動モード；NO ACTION、検査契機モード；猶予モード」であると分かる。図3「参照制約機能を利用する以前の更新手順①」を参照すると「”部署”テーブルから不要な行を削除」とある。つまり、”部署”テーブルから行を削除した際、CASCADEにより、参照元の”従業員”テーブルの”部署コード”も削除される。よって、「⑤”従業員”テーブルの部署コードを更新」を前に、更新すべき行がない状態となってしまう不具合が発生する。
したがって、契機『①』で、『』

＜問題＞
・参照先テーブルの外部キーが削除されると、設問3（1）は、更新手順の設計ミスによる不具合の内容の説明問題である。
＜条件＞
・図3「参照制約機能を利用する以前の更新手順」参照
・テーブル名；従業員の列名；部署コード、参照先；部署（部署コード）、実行契機；DELETE、挙動モード；CASCADE、検査契機モード；猶予モード（表3「参照制約機能の利用案」参照）

＜回答＞
表3「参照制約機能の利用案」を参照すると、「テーブル名；従業員｜列名；部署コード、参照先；部署（部署コード）、実行契機；UPDATE、挙動モード；NO ACTION、検査契機モード；猶予モード」であると分かる。図3「参照制約機能を利用する以前の更新手順①」を参照すると「”部署”テーブルから不要な行を削除」とある。
つまり、”部署”テーブルから行を削除した際、CASCADEにより、参照元の”従業員”テーブルの”部署コード”も削除される。候補キーの一部の”部署コード”が削除されることにより、対象の候補キーを含む行は削除される。よって、「⑤”従業員”テーブルの部署コードを更新」を前に、更新すべき行がない状態となってしまう不具合が発生する。
したがって、契機『①』で、『削除された部署に所属していた従業員が”従業員”テーブルから削除される』という不具合が発生する。

ポイント



＜ポイント＞
・参照先テーブルの外部キーが削除されると、CASCADEにより、参照元テーブルの外部キーの元となった候補キーの『行全体』が削除される。候補キーが削除されることにより、行も削除される訳ではなし。→DELETEのCASCADEでは”行ごと”削除
・参照元テーブルの外部キーが削除された場合、参照元テーブルの外部キーがNULLとなるだけ。

参照制約のDELETE操作ポイント整理

操作対象のテーブル削除される行/キーの役割挙動モード結果と正確な動作
参照先（親） (部署) の行を削除主キー (部署コード) の行を削除CASCADE参照元 (従業員) の対応する行（外部キー含む）が、行全体で連鎖的に削除されます。外部キー列だけが削除されるわけではありません。
参照先（親） (部署) の行を削除主キー (部署コード) の行を削除SET NULL参照元 (従業員) の対応する行の外部キー (部署コード) の値がNULLに更新されます。行自体は削除されません。
参照元（子） (従業員) の行を削除外部キー (部署コード) を含む行全体を削除（モード無関係）正常終了。参照制約違反は発生せず、参照先（部署）の主キーはそのまま維持されます。外部キーの値は「NULLになる」のではなく、**「値ごと消滅」**します。

設問3（2）

＜問題＞
設問3（2）は（1）の不具合解消のための順序を答える問題。

＜回答＞
先に部署を削除することで、削除する部署に所属していた従業員が削除されるのが問題なので、先に”従業員”テーブルの”部署コード”を変えて、その後に不要な”部署コード”を削除すれば、従業員は削除されない。
したがって、『③”部署”テーブルに新規行を追加→⑤’従業員’テーブルの”部署コード”を更新→①”部署”テーブルから不要な行（不要な部署）を削除』となる。

ポイント

・『新規行挿入』→『更新』→『削除』；不具合なし
・『削除』→『更新』→『新規行追加』；削除時に参照元テーブルも一緒に削除される（CASCADEの場合）

更新手順の安全性のポイント

手順の原則動作の安全性理由（CASCADE設定時）
新規 → 更新 → 削除安全更新（異動）を先に行うことで、後の削除操作から従業員データを切り離し、連鎖削除を防ぐ。
削除 → 更新 → 新規不安全削除を先に行うとCASCADEで行全体が消滅し、その後の更新操作が不可

設問3（3）

＜問題＞
設問3（3）は、参照制約；猶予モードにおける更新時の振る舞いについての問題。
＜条件＞
・”従業員”テーブルの”部署コード”の参照制約；猶予モード
・”部署”テーブルの”部署コード”を更新した時
・（a）猶予モードの制約検査はどのようなものか？
・（b）（a）の検査の際、処理時間が長くなると予想される理由は？

＜回答＞
まず、（a）について、処理の確認する。
表3「参照制約機能の利用案」から「テーブル名；従業員の列名；部署コード、参照先；部署（部署コード）、実行契機；UPDATE、挙動モード；NO ACTION、検査契機モード；猶予モード」となっている。
”部署”テーブルの”部署コード”更新SQL実行時、挙動モード；NO ACTIONのため、”従業員”（参照元）テーブルには反映されない。
全SQLの終了後、トランザクション終了時、検査契機モード；猶予モードが適用される。この時行われるのは、『参照制約違反かどうか』　である。つまり、参照先の”部署コード”を更新した場合、参照元の”部署コード”は『参照先の”部署コード”になっているか』もしくは『NULL（非NULL制約はない上で）』になっている必要がある。
したがって、『更新によって無くなった部署コードが、”従業員”テーブルの”部署コード”に存在しないことを確認する』や『猶予モードでは、トランザクション終了時に、従業員テーブルの部署コードがすべて、部署テーブルの部署コード（またはNULL）に対応しているかをまとめて検査する』となる。

次に（b）処理時間が長くなると考えられる理由について考える。
今回の場合、”従業員”テーブルの”部署コード”について、検査することになるが、この処理のために対象の”部署コード”に絞って検査するのが最も早い。
しかし、〔人事情報管理データベースのテーブル〕に「索引は主キーだけに定義」とある。
よって、”従業員”テーブルの全ての行を検査する必要があり、これにより処理時間が長くなると考えられる。
したがって、『”従業員”テーブルの”部署コード”に索引がなく、”従業員”テーブルの全行に対して検査を行わなければならないから』となる。

ポイント


＜ポイント＞
・猶予モードの検査内容→参照制約についての検査
・検査契機モードと挙動モードの関係（検査契機モードは参照制約のタイミングの話で、挙動モードは、更新時の反映の仕方や有無の話？）
＜挙動モード；NO ACTION｜検査契機モード；猶予モード＞
更新時；参照元へ反映しない。検査制約をトランザクション終了時に行う。
＜挙動モード；NO ACTION｜検査契機モード；即時モード＞
更新時；参照元へ反映しない。検査制約を各SQL終了時に行う。

＜挙動モード；CASCADE｜検査契機モード；猶予モード＞
更新時；参照元へ反映する。検査制約をトランザクション終了時に行う。
＜挙動モード；CASCADE｜検査契機モード；即時モード＞
更新時；参照元へ反映する。検査制約を各SQL終了時に行う。

組み合わせ挙動（データの反映）制約検査のタイミング主な特徴
NO ACTION × 即時モード反映しない各SQL実行後に即検査最も厳格。整合性維持は強いが柔軟性は低い
NO ACTION × 猶予モード反映しないトランザクション終了時に検査トランザクション内で一時的な不整合を許容
CASCADE × 即時モード自動反映する各SQL実行後に即検査更新・削除が即時に連鎖反映
CASCADE × 猶予モード自動反映するトランザクション終了時に検査大量更新・バッチ処理向き

タイトルとURLをコピーしました