目次
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 | 商品Ａ |
| 2 | 商品Ｂ |
| 3 | 商品Ｃ |
+--------+--------+ - 予約
+--------+--------+
| 予約ID | 商品ID |
+--------+--------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
+--------+--------+
※商品IDに外部キー定義を行う。 - 予約テーブルに商品Ａの注文を追加
これは成功する。 - 予約テーブルに商品未定(商品ＩＤ=NULL)の注文を追加
これは成功する。 - 予約テーブルに商品Ｄ(商品ＩＤ=4)の注文を追加
これは失敗する。 - オプション無しで商品テーブルの商品Ａの商品IDを3に変更
これは失敗する。 - オプション無しで商品テーブルの商品Ａのレコードを削除
これは失敗する。 - オプション無しで商品テーブルの商品Ｃのレコードを削除
これは成功する。
（予約テーブルで参照されていないレコードのため） - CASCADEオプションで商品テーブルの商品Ａの商品IDを3に変更
これは成功する。予約テーブルは下記のように更新される。
+--------+--------+
| 予約ID | 商品ID |
+--------+--------+
| 1 | 3 |
| 2 | 2 |
| 3 | 3 |
+--------+--------+ - CASCADEオプションで商品テーブルの商品Ａのレコードを削除
これは成功する。予約テーブルは下記のように更新される。
+--------+--------+
| 予約ID | 商品ID |
+--------+--------+
| 2 | 2 |
+--------+--------+ - SET NULLオプションで商品テーブルの商品Ａの商品IDを3に変更
これは成功する。予約テーブルは下記のように更新される。
+--------+--------+
| 予約ID | 商品ID |
+--------+--------+
| 1 | NULL |
| 2 | 2 |
| 3 | NULL |
+--------+--------+ - SET NULLオプションで商品テーブルの商品Ａのレコードを削除
これは成功する。予約テーブルは下記のように更新される。
+--------+--------+
| 予約ID | 商品ID |
+--------+--------+
| 1 | NULL |
| 2 | 2 |
| 3 | NULL |
+--------+--------+
