SGの主キー・外部キーの違い｜部署と社員で参照整合性を図解

現行SGシラバスVer.4.1のデータベース分野には、関係データベース、DBMS、データ重複の排除、SQLなどが挙がっています。本記事は、その学習の前提になる「どの行を指すか」「別の表のどの行と対応するか」を具体化する解説です。主キー・外部キーの全ての仕様がシラバスの用語例に列挙されている、又は必ずこの形で出題される、という説明ではありません。

例に使う部署・社員・識別子は全て当サイト独自の架空データです。実在の利用者や企業、公式問題・過去問の再現ではありません。PostgreSQL 18の公式資料を参照し、関係の読み方と制約の効果を説明しますが、実DBを作成・更新・削除する操作手順ではありません。

学習では、同じ行を特定する仕組みと、複数の行が同じ部署を参照する仕組みを区別します。「キー」という名前が付けば全て重複禁止、主キーは必ず数字一列、外部キーは別の表の主キーだけを参照する、といった一律の暗記は避けます。

複数の表や受付元を分析へつなぐ学習はSGのデータウェアハウス・ETLで、行の対応に加えて、集計の単位と対象範囲をそろえる独自ケースを確認できます。

部署表には、部署ID D10・部署名「総務」と、部署ID D20・部署名「営業」の二行があります。部署IDを主キーとします。社員表には社員IDと所属部署IDを持たせ、社員IDを主キー、所属部署IDを部署表の部署IDへの外部キーとします。このケースでは、所属を必須として所属部署IDへNOT NULLも設定する条件です。

社員E01とE02はD10、社員E03はD20を参照します。同じ部署に複数の社員がいるため、社員表の所属部署IDにD10が二回現れても、この設計では誤りではありません。重複してはいけない社員IDと、複数行が同じ値を使う所属部署IDを混同しないようにします。

名前で識別すると、同名の人や名称の変更で扱いに迷うことがあります。この例はIDで行を識別し、部署名は部署表で持ちます。ただし、IDを採用したことだけで重複や誤参照が防げるわけではありません。識別と参照の制約を、実際に設定して守ることが前提です。

主キーは、表の各行を一意に識別する列、又は列の組合せです。PostgreSQLの主キーは重複とNULLを許しません。社員E01がすでにある状態で、別の社員の行へ同じ社員ID E01を付けたり、社員IDをNULLにしたりする追加は、この主キー制約に反します。部署IDが正しくても、社員ID側の問題は別に残ります。

別の架空例として、研修への参加表で「研修IDと社員IDの組」を主キーにする設計を考えます。同じ研修に異なる社員が参加でき、同じ社員が異なる研修に参加できても、同じ研修と同じ社員の組合せは重複できません。二つの列を使う一つの主キーであり、主キーが二つあるという意味ではありません。

また、主キーにする列以外にも、一意であるべき値へ一意制約を設定できます。PostgreSQLでは外部キーの参照先に、条件を満たす一意な列・列の組合せも使えます。本記事は説明を単純にするため部署の主キーを参照していますが、外部キーの参照先は必ず主キーだけ、と一般化しないようにします。

社員表へE04・D99を追加しようとしても、部署表にD99がなければ、本例の外部キー制約が拒否します。こうして、参照する行が存在しない対応を防ぐ考え方が参照整合性です。一方、E04・D20なら参照先は存在しますが、本当の所属が総務であれば業務上は誤登録です。存在確認と、内容の事実確認は別です。

NULLの扱いも区別します。本例の所属部署IDはNOT NULLなので、所属なしの行を登録できません。これとは別の設計としてNOT NULLを付けず、通常の一列の外部キー制約だけを使う場合、NULLは許されます。外部キーと必須入力は同じ制約ではありません。NULLを文字列「NULL」、空文字、数値0と同じ値として扱うものでもありません。

参照整合性を守っても、入力する人にその操作権限があることや、個人情報をその目的で利用してよいことまで保証されません。誰が登録・変更できるか、変更の根拠を誰が確認するかは、認証・権限や業務手順として別に管理します。DBの制約を、全ての安全管理や業務チェックの代わりにはしません。

D10の部署名だけを「総務」から「管理」へ変え、IDはD10のままにする例を考えます。社員表がIDだけで所属を持つこの設計では、E01とE02の所属部署IDを変える必要はありません。部署表と対応付けて最新名を表示すれば「管理」になります。表示名の変更と、識別子の変更を同じものとして扱わないことが要点です。

D10の行そのものを削除する場合は、社員からの参照が残っています。PostgreSQLで削除時の動作をRESTRICTにしていれば、その参照先の削除は拒否されます。CASCADEにしていれば、参照する社員E01とE02の行も削除されます。外部キーがあれば自動で必ずどちらかの望ましい結果になる、とは考えません。

この比較は設定の結果を学ぶためのもので、社員情報の削除を推奨するものではありません。保存すべき記録や業務上の意味から、削除を認めるか、別の有効状態で管理するかも検討します。なお、既定のNO ACTIONとRESTRICTには制約の検査時点などの違いがあるため、完全な同義語としては扱いません。

復習では、各表の主キーをまず囲み、外部キーからどの表のどの列へ対応するかを書きます。その後で、重複・NULL・参照先の存在を別々に確認します。表の見た目だけでキーを推定するのではなく、問題文や定義で何を制約としているかへ戻ることが重要です。

たとえば部署名を社員表にも複写していた場合は、最新名を対応付けて表示する本例とは更新の条件が変わります。別の表や履歴を勝手に補わず、与えられた列と関係だけで判断します。制約が満たされたことと、事実や目的に合う登録であることを分け、足りない条件は未確認として残してください。

複数の表を更新する単位と同時実行の学習はSGのトランザクションと排他制御、業務へ組み込む確認の位置付けはSGの内部統制とIT統制へ。参照整合性、更新の確定単位、担当者の権限を一つの機能の名前として扱わないようにしましょう。

範囲はSGシラバス案内、文章の条件整理は科目Bの読み方で確認できます。登録不要の無料6問は学習画面の体験で、キーの理解を網羅的に診断するものではありません。SGの教材・料金で収録範囲と購入条件をご確認ください。