この記事は何の記事 ?
データベーススペシャリスト試験対策の記事です
外部キーと参照制約について書いてます
また問題になる例も挙げてます
外部キーとは
簡単に
簡単にいうと別のテーブルの主キーのことです
例を見てみましょう
下線が主キーで，点線が外部キーです
だから何だ
別のテーブルの主キーなので，
別のテーブルに存在しない主キーが，
外部キーとして存在している時に怒られたり怒られなかったりします
下図の例の場合，背景が黄色のセルに注目してください
参照元の外部キーとして，参照先コードを使用するわけですが，
「先9」は参照先テーブルに存在しません
これが問題になったりならなかったりします
参照制約
参照元の外部キーが，参照先の主キーに存在していることが参照制約です
大事なのが以下です
- どのタイミングで（実行契機）
- 外部キーをどう処理するか（挙動）
- いつ参照制約の確認をするのか（検査契機）
実行契機
主に以下の 3 つです
- 挿入（INSERT）
- 更新（UPDATE）
- 削除（DELETE）
注意したいのは，試験で実行契機について記載がない場合は，
そのタイミングでは外部キーに対して特に何もしないということです
例えば，更新と削除についてのみ記載があった場合は，
挿入については外部キーが何だろうと何もしないというわけですね
因みに当然ですが，挿入と更新は別物なので気を付けてください...
挙動
実行契機のイベントが発生した時に，
定められたアクションをします
アクションは主にこんな感じです
| アクション | 説明 |
|---|---|
| NO ACTION | 参照元のテーブルにイベントが発生したとき，参照先のテーブルに対して何もしない |
| CASCADE | 参照元のテーブルにイベントが発生したとき，参照先のテーブルにもイベントを連鎖させる |
他にも，参照先のテーブルに NULL をセットだったり，デフォルト値をセットだったり，するアクションもあります
こちらが参考になります
検査契機
SQL 実行終了ごとだったり，トランザクション終了時だったりします
いずれにせよ確認はしているわけです（ここが大事）
表の見方
試験で出る表の見方です
- イベントが発生した「参照先」を参照している「参照元」を探します
- 実行契機と挙動モードを確認します
具体例を見てみましょう
最初に，「参照先」の指定した行を「更新」した場合です
- 「参照先」を参照している「参照元」を特定
- 実行契機と挙動を確認
- 今回は「更新」なので「UPDATE」を確認
- 挙動モードは「NO ACTION」でした
- NO ACTION なので参照元のデータはそのまま残しておきます
問題になる例
なぜお前がここに居る
削除時のアクションが，NO ACTION だった場合ですね
下記をご覧ください
「参照先」の「先1」が削除された時に，
NO ACTION なので「参照元」の外部キー「先1」が残ってしまっています
この状態で参照元テーブルをもとに集計などをしていると，
参照先テーブルにいないデータもいることになってしまい，
なぜお前がここに...と不具合が起きてしまうわけですね
霊圧が…消えた…？
削除時にアクションが，CASCADE だった場合ですね
下記をご覧ください
「参照先」の「先1」が削除された時に，
CASCADE なので「先1」を参照している「元a」が削除されます
例えば，
参照先の「先1」を削除して，
参照元で外部キーが「先1」の子を「先2」に変更したいとします
ここで，最初に参照先の「先1」を削除すると，
CASCADE で参照元で外部キーが「先1」の子も削除されます
すると，参照元で外部キーが「先1」の子を「先2」に変更しようとしても，
既に削除されているので変更できない...
というか削除されちゃいけない子なのに...
という事態になります