技術とか戦略とか

証券レガシーシステムを8年間いじった普通のSEによるブログ。技術のみではなく趣味の戦略考察についても。PCから見た方が色々見やすいと思います。

情報処理技術者試験対策「SQL(外部キー)」

目次

https://1drv.ms/b/s!AivF3bzWXOzuhG1Xk5hscKYqkLkM

-------------------------------
今回は、SQLの外部キー(参照制約)について書きます。
外部キーはPG・SEなら知っておくべき基礎的な文法だと思うのですが、実務だと使わない現場ではとことん使わない文法で、そのような現場で働いていると忘れがちになるので。
 
【外部キーを定義した時の挙動】
テーブルAのある列が、テーブルBのある列を参照している場合、テーブルAの当該の列を外部キーに指定することができます。
オプションを指定しない場合は下記の挙動になります。

  • テーブルAにレコードを追加(INSERT文を発行)する場合、外部キーを指定した列の値が、テーブルBに登録されている値かNULLでなければ、レコード追加に失敗する。
  • テーブルBのレコードを更新(UPDATE文を発行)・削除(DELETE文を発行)する場合、テーブルAにその値を持っているレコードが存在するなら、レコード更新・削除に失敗する。

オプションとしては以下のものがあります。
テーブルBのレコードを更新または削除する場合の挙動を指定できます。

  • NO ACTION
    オプション未指定時と同じ挙動になる。
  • CASCADE
    テーブルAにその値を持っているレコードが存在する場合においても更新・削除が可能。
    テーブルBの値更新に連動してテーブルAの同じ値を持つ列の値が更新されたり、テーブルBのレコード削除に連動してテーブルAの同じ値を持つレコードが削除されたりする。
  • SET NULL
    テーブルAにその値を持っているレコードが存在する場合においても更新・削除が可能。
    テーブルAの同じ値を持つ列の値はNULLに更新される。


【外部キー定義の構文】
FOREIGN KEY (テーブルAの列名)
REFERENCES テーブルB名(列名)
[ON UPDATE {NO ACTION|CASCADE|SET NULL}]
[ON DELETE {NO ACTION|CASCADE|SET NULL}]
 
【テーブル定義の例】

  • 参照元テーブル(テーブルB)の定義
    create table 商品(
    商品ID INT PRIMARY KEY,
    商品名 CHAR(50)
    );
  • 参照先テーブル(テーブルA)の定義
    (列制約、オプション無しの場合)
    create table 予約(
    予約ID INT PRIMARY KEY,
    商品ID INT REFERENCES 商品(商品ID)
    );
    (テーブル制約、DELETE時にSET NULLオプションをつける場合)
    create table 予約(
    予約ID INT PRIMARY KEY,
    商品ID INT,
    FOREIGN KEY(商品ID)
    REFERENCES 商品(商品ID)
    ON DELETE SET NULL;

【テーブル操作の例】

  • 商品
    +--------+--------+
    | 商品ID | 商品名 |
    +--------+--------+
    | 1      | 商品A |
    | 2      | 商品B |
    | 3      | 商品C |
    +--------+--------+
  • 予約
    +--------+--------+
    | 予約ID | 商品ID |
    +--------+--------+
    | 1      | 1      |
    | 2      | 2      |
    | 3      | 1      |
    +--------+--------+
     ※商品IDに外部キー定義を行う。
  • 予約テーブルに商品Aの注文を追加
    これは成功する。
  • 予約テーブルに商品未定(商品ID=NULL)の注文を追加
    これは成功する。
  • 予約テーブルに商品D(商品ID=4)の注文を追加
    これは失敗する。
  • オプション無しで商品テーブルの商品Aの商品IDを3に変更
    これは失敗する。
  • オプション無しで商品テーブルの商品Aのレコードを削除
    これは失敗する。
  • オプション無しで商品テーブルの商品Cのレコードを削除
    これは成功する。
    (予約テーブルで参照されていないレコードのため)
  • CASCADEオプションで商品テーブルの商品Aの商品IDを3に変更
    これは成功する。予約テーブルは下記のように更新される。
    +--------+--------+
    | 予約ID | 商品ID |
    +--------+--------+
    | 1      | 3      |
    | 2      | 2      |
    | 3      | 3      |
    +--------+--------+
  • CASCADEオプションで商品テーブルの商品Aのレコードを削除
    これは成功する。予約テーブルは下記のように更新される。
    +--------+--------+
    | 予約ID | 商品ID |
    +--------+--------+
    | 2      | 2      |
    +--------+--------+
  • SET NULLオプションで商品テーブルの商品Aの商品IDを3に変更
    これは成功する。予約テーブルは下記のように更新される。
    +--------+--------+
    | 予約ID | 商品ID |
    +--------+--------+
    | 1      | NULL   |
    | 2      | 2      |
    | 3      | NULL   |
    +--------+--------+
  • SET NULLオプションで商品テーブルの商品Aのレコードを削除
    これは成功する。予約テーブルは下記のように更新される。
    +--------+--------+
    | 予約ID | 商品ID |
    +--------+--------+
    | 1      | NULL   |
    | 2      | 2      |
    | 3      | NULL   |
    +--------+--------+