SQLの外部キーをマスター！概要から使用方法までを解説

外部キー（FOREIGN KEY）

外部キー（FOREIGN KEY）は、テーブルのカラムに対して設定する制約で、多くのRDB（リレーショナルデータベース）が外部キーをサポートしています。

カラムに外部キーを設定すると、制約を設定したカラムには、参照先のテーブルに格納されている値しか格納できないように限定できます。

また、参照先のテーブルからレコードを変更・削除する場合も、外部キーを設定したカラムで使用されている場合は、値が変更できないようになります。

外部キーの利用例

外部キーは、参照される側のテーブルと、外部キーを設定したカラム（参照する側のテーブル）との間でデータの整合性を保つときに用いる制約です。

また、外部キーの設定する時は、「参照される側／する側」の関係を分かりやすく「親テーブル／子テーブル」と呼ぶことが多いようです。

外部キーの利用例として、次のような「従業員テーブル」と「部署マスタ」の間で外部キーを設定する例を見ていきましょう。

参照する側（子テーブル）の部署コードに対し、参照される側（親テーブル）の部署コードを指定して、外部キー（FOREIGN KEY ）制約を設定します。

上記のような外部キー（FOREIGN KEY ）制約を設定することで、従業員テーブルの部署コードには、部署マスタにある部署コードしか設定できなくなります。
もし、部署マスタに存在しない部署コードを、従業員テーブルに設定してレコードを登録しようとした場合、SQLエラーとなりデータが登録できません。

また、従業員テーブルで使用している部署コードを、親テーブルとなる部署マスタ側で変更しようとした場合に、エラーを表示するか、参照する側の従業員テーブルの部署コードを、変更後の値で一緒に更新するか、NULL値を挿入することができます。

このように、外部キー（FOREIGN KEY ）制約を設定することで、２つのテーブル間のデータ整合性がデータベースレベルで保証されるようになり、これらのデータを参照するアプリケーション開発を容易にすることが期待できます。

外部キー（FOREIGN KEY ）制約の設定方法

それでは、実際に外部キー（FOREIGN KEY ）制約を設定するスクリプトを作成して、テーブルに外部キーを設定し、実際のデータベースの動作を確認してみましょう。

今回は、MySQLデータベース上に、前述の利用例と同じように、「従業員テーブル」と「部署マスタ」を作成し、部署コードに外部キーを設定する例を紹介します。

テーブルを作成

外部キーを設定するためには、当然ながらテーブルが必要です。以下のCREATE TABLEを使用してデータベース上に２つのテーブルを作成し、テスト用のデータも挿入します。

部署マスタを作成するCREATE TABLE文およびテストデータ作成用のINSERT文

CREATE TABLE depts (
   dept_cd     char(3),        -- 部署コード
   dept_name   varchar(255),   -- 部署名
   PRIMARY KEY (dept_cd)       -- 主キー（部署コード）
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO depts VALUES('B01', '営業部');
INSERT INTO depts VALUES('B02', '広報部');
INSERT INTO depts VALUES('B03', '人事部');
INSERT INTO depts VALUES('B04', '企画部');

従業員テーブルを作成するCREATE TABLE文およびテストデータ作成用のINSERT文

CREATE TABLE employees (
   emp_id     varchar(20),     -- 従業員ID
   emp_name   varchar(255),    -- 氏名
   dept_cd    char(3),         -- 部署コード
   PRIMARY KEY (emp_id)        -- 主キー（従業員ID）
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO employees VALUES('1001', 'YAMADA','B01');
INSERT INTO employees VALUES('1002', 'SUZUKI','B01');
INSERT INTO employees VALUES('1003', 'ITO','B02');
INSERT INTO employees VALUES('1004', 'KATO','B03');

外部キーを設定

テーブルを作成したら、次のALTER文を実行して、従業員テーブルの部署コードから、部署マスタの部署コードを参照する外部キー（FOREIGN KEY ）制約を設定しましょう。

ALTER TABLE employees
    ADD FOREIGN KEY
    REFERENCES depts (dept_cd); ---- 部署マスタの部署コードを参照先に設定

外部キーは、CREATE TABLEでテーブルを作成するときに、一緒に作成することもできます。上の外部キーをCREATE TABLEで設定するには、次のように記述します。

CREATE TABLE employees (
   emp_id     varchar(20),     -- 従業員ID
   emp_name   varchar(255),    -- 氏名
   dept_cd    char(3),         -- 部署コード
   PRIMARY KEY (emp_id),        -- 主キー（従業員ID）
   -- ↓ ここで外部キーを指定 ↓ --
   FOREIGN KEY(dept_cd) -- 外部キー
    REFERENCES depts(dept_cd) -- 部署マスタの部署コードを参照先に設定
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

参照される側（親テーブル）に存在しないキーを登録する

外部キー（FOREIGN KEY ）制約を設定すると、参照される側（親テーブル）に存在したい値を登録すると、SQLエラーとなり登録できません。

次のINSERT文を実装し、部署マスタに存在しない部署コードを指定して、従業員マスタを登録してみましょう。

INSERT INTO employees VALUES('1005', 'MORI','Z99');

＜実行結果＞
ERROR 1452 (23000):

このように、INSERT文がエラーとなり、データが登録できません。

同じように、次はUPDATE文を使って、従業員テーブルの部署コードに、部署マスタに存在しない値を設定してみます。

UPDATE employees
SET dept_cd = 'Z99'
WHERE emp_id = '0001';

＜実行結果＞
ERROR 1452 (23000):

UPDATE文でもエラーとなることが確認できます。

参照される側（親テーブル）のレコードを更新/削除する

次は、参照される側（親テーブル）のレコードを更新/削除した時の動作についてです。

部署マスタのように、参照される側（親テーブル）のレコードを更新または削除しようとした時、その値が参照する側（子テーブル）で使用されている場合、エラーで更新または削除できません。

ただし、外部キーを作成する時に、次のようなreference_optionを指定することにより、その振る舞いを変更させることができます。

ALTER TABLE employees
    ADD FOREIGN KEY
    REFERENCES depts (dept_cd)
    [ON DELETE reference_option]   --レコード削除時の振る舞い
    [ON UPDATE reference_option])  --レコード更新時の振る舞い

reference_optionに指定可能な値は次の通りです。指定を省略した場合はRESTRICT が指定された場合と同じ動きになります。

RESTRICT

子テーブルで使用されている値を、親テーブル側で削除/更新するとエラーになります。指定を省略した場合は RESTRICT がデフォルト値です。

CASCADE

親テーブル側で削除/更新すると、子テーブルにも同じ値を削除または更新します。

SET NULL

親テーブル側で削除/更新すると、子テーブルの同じ値を持つカラムをNULLで更新します。

まとめ

外部キー（FOREIGN KEY）の概要や、作成方法について解説してきました。

外部キーを利用することで、データベースレベルでデータの整合性が保たれるため、アプリケーション側でデータの生合成について考慮が必要なくなり、アプリケーション開発の生産性向上も期待できます。