リレーショナルデータベースの外部キーの使い方 オプションも紹介！

この記事では、リレーショナルデータベースで使われる「外部キー」について、SQLの実行例を交えながら紹介します。
外部キーとは、関連したテーブル間でデータの整合性を保つために、関連のあるカラムに対して付与する制約です。
現場によっては使わない文法ですが、使うべきだとする論調は根強く、また情報処理技術者試験でも問われるので、技術者であれば知っておいた方が良い内容です。

---

以下では、外部キーの具体的な挙動、構文、使用例について、詳しく述べていきます。

【外部キーを定義した時の挙動】

テーブル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)
);

・参照先テーブル（テーブルA）の定義（テーブル制約、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   |
+--------+--------+

【あとがき】