この記事では、リレーショナルデータベースで使われる「外部キー」について、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に更新される。 |
【外部キー定義の構文】
|
1 2 3 4 |
FOREIGN KEY (テーブルAの列名) REFERENCES テーブルB名(列名) ［ON UPDATE {NO ACTION|CASCADE|SET NULL}］ ［ON DELETE {NO ACTION|CASCADE|SET NULL}］ |
【テーブル定義の例】
・参照元テーブル（テーブルB）の定義
|
1 2 3 4 |
CREATE TABLE 商品( 商品ID INT PRIMARY KEY, 商品名 CHAR(50) ); |
・参照先テーブル（テーブルA）の定義（列制約、オプション無しの場合）
|
1 2 3 4 |
CREATE TABLE 予約( 予約ID INT PRIMARY KEY, 商品ID INT REFERENCES 商品(商品ID) ); |
・参照先テーブル（テーブルA）の定義（テーブル制約、DELETE時にSET NULLオプションをつける場合）
|
1 2 3 4 5 6 |
CREATE TABLE 予約( 予約ID INT PRIMARY KEY, 商品ID INT, FOREIGN KEY(商品ID) REFERENCES 商品(商品ID) ON DELETE SET NULL; |
【テーブル操作の例】
・商品テーブルのレコード
|
1 2 3 4 5 6 7 |
+--------+--------+ | 商品ID | 商品名 | +--------+--------+ | 1 | 商品Ａ | | 2 | 商品Ｂ | | 3 | 商品Ｃ | +--------+--------+ |
・予約テーブルのレコード
|
1 2 3 4 5 6 7 |
+--------+--------+ | 予約ID | 商品ID | +--------+--------+ | 1 | 1 | | 2 | 2 | | 3 | 1 | +--------+--------+ |
※商品IDに外部キー定義を行う。
・予約テーブルに商品Ａの注文を追加
これは成功する。
・予約テーブルに商品未定(商品ＩＤ=NULL)の注文を追加
これは成功する。
・予約テーブルに商品Ｄ(商品ＩＤ=4)の注文を追加
これは失敗する。
・オプション無しで商品テーブルの商品Ａの商品IDを3に変更
これは失敗する。
・オプション無しで商品テーブルの商品Ａのレコードを削除
これは失敗する。
・オプション無しで商品テーブルの商品Ｃのレコードを削除
これは成功する。
（予約テーブルで参照されていないレコードのため）
・CASCADEオプションで商品テーブルの商品Ａの商品IDを3に変更
これは成功する。予約テーブルは下記のように更新される。
|
1 2 3 4 5 6 7 |
+--------+--------+ | 予約ID | 商品ID | +--------+--------+ | 1 | 3 | | 2 | 2 | | 3 | 3 | +--------+--------+ |
・CASCADEオプションで商品テーブルの商品Ａのレコードを削除
これは成功する。予約テーブルは下記のように更新される。
|
1 2 3 4 5 |
+--------+--------+ | 予約ID | 商品ID | +--------+--------+ | 2 | 2 | +--------+--------+ |
・SET NULLオプションで商品テーブルの商品Ａの商品IDを3に変更
これは成功する。予約テーブルは下記のように更新される。
|
1 2 3 4 5 6 7 |
+--------+--------+ | 予約ID | 商品ID | +--------+--------+ | 1 | NULL | | 2 | 2 | | 3 | NULL | +--------+--------+ |
・SET NULLオプションで商品テーブルの商品Ａのレコードを削除
これは成功する。予約テーブルは下記のように更新される。
|
1 2 3 4 5 6 7 |
+--------+--------+ | 予約ID | 商品ID | +--------+--------+ | 1 | NULL | | 2 | 2 | | 3 | NULL | +--------+--------+ |
【あとがき】
今回の記事は、実務で外部キーを使わない方にとってはあまり馴染みが無いものだったかもしれません。
また、理解するためには、リレーショナルデータベースについて、テーブルを定義するDDLの知識や、参照・挿入・更新・削除のDMLの知識が必要です。
今回の記事が難しく感じられた方には、株式会社サイゼントの書籍やプログラミングスクールがお勧めです。
株式会社サイゼントでは、即戦力のJavaプログラマーを育てるための書籍「絶対にJavaプログラマーになりたい人へ」をKindleで販売しています。
また、上記の書籍をテキストとして用いたプログラミングスクール「サイゼントアカデミー」も開校しています。
このスクールは、受託開発事業・SES事業である弊社が、新入社員向けの研修で培ったノウハウを詰め込んだものです。
ご興味がある方は、上記画像から個別ページにアクセスしてみてください！
コメント