外部キー制約はテーブル設計において重要な役割を持ちますが、理解があいまいなまま使ってしまっていました。整合性の取れたテーブル設計をするため、定義や使い方をまとめておきたいと思います。
使用しているのは MySQL です。
目次
1. 外部キー制約とは
2. 外部キー制約を設定する
3. 外部キー制約を設定した場合の動作
4. 親テーブルの変更を子テーブルにも反映させる
1. 外部キー制約とは
例えば以下のように、部署テーブルと社員テーブルがあるとします。
社員テーブルには、各社員が所属する部署を管理するための、部署IDカラムがあります。ここに部署テーブルに存在しない部署IDが入ってしまうと、社員がどの部署に所属しているのかわからなくなってしまいます。
そこで、社員テーブルの部署IDカラムに入れることができるのは、部署テーブルに存在する部署IDのみとしたいです。その場合に、社員テーブルの部署IDカラムに外部キーを設定します。
【 部署テーブル (親テーブル) 】
| 部署ID | 部署名 |
|---|---|
| 1 | 総務部 |
| 2 | 経理部 |
| 3 | 営業部 |
| 4 | 企画部 |
| ... | ... |
【 社員テーブル (子テーブル) 】
| 社員ID | 社員名 | 部署ID (ここに外部キーを設定！) |
|---|---|---|
| 1 | 田中 | 3 |
| 2 | 山田 | 2 |
| 3 | 佐藤 | 3 |
| 4 | 鈴木 | 1 |
| ... | ... | ... |
外部キーを設定して参照する側を子テーブル (ここでは社員テーブル)、参照される側を親テーブル (ここでは部署テーブル)と言います。
外部キーは、参照する側の子テーブルのカラムに設定します。 そうすることで、子テーブルで外部キーを設定したカラムのデータは、親テーブルで参照元として設定したカラム内に存在するデータであることが保証されます。
外部キーを設定することで、以下の例のような制約がつきます。
- 社員テーブルの部署IDカラムには、部署テーブルの部署IDカラムに存在しない部署IDは入れられなくなります。
- 部署テーブルの該当カラムが削除されたり変更があった場合、社員テーブルの部署IDカラムにも変更が適切に反映されるように設定することができます。(4. 親テーブルの変更を子テーブルにも反映させる)
- 2 の設定をしない場合は、社員テーブルの部署IDの参照元がなくなってしまうことを防ぐため、部署テーブルの該当カラムの削除や変更はできなくなります。
ちなみにややこしいのですが、参照する側を参照先、参照される側を参照元と言うみたいです。
2. 外部キー制約を設定する
実際にテーブルを作ってみます。
参照する側である子テーブルを先に作ることはできません。参照元である親テーブルがまだないためエラーになります。 先に親テーブルを作成することに注意しましょう。
【 部署テーブル (親テーブル) 】
CREATE TABLE departments (
PRIMARY KEY (id), -- 主キーの設定
id INT AUTO_INCREMENT, -- 部署ID
name VARCHAR(20) -- 部署名
);
【 社員テーブル (子テーブル) 】
CREATE TABLE employees (
PRIMARY KEY (id), -- 主キーの設定
id INT AUTO_INCREMENT, -- 社員ID
name VARCHAR(20), -- 社員名
department_id INT, -- 部署ID
FOREIGN KEY (department_id) REFERENCES departments(id) -- 外部キーの設定
);
FOREIGN KEY (department_id) REFERENCES departments(id) で外部キーの設定をしています。
外部キーとして設定するカラム department_id と、参照元の親テーブルのテーブル名とカラム名 departments(id) を指定しています。
3. 外部キー制約を設定した場合の動作
テーブルができたので、どのような動作をするか確認していきます。
まだテーブルを作っただけなので、まずは部署テーブルにレコードを入れます。
INSERT INTO departments (name) VALUES
('総務部'),
('経理部'),
('営業部'),
('企画部');
内容を確認しておきます。
SELECT * FROM departments;
出力結果：
+----+-----------+
| id | name |
+----+-----------+
| 1 | 総務部 |
| 2 | 経理部 |
| 3 | 営業部 |
| 4 | 企画部 |
+----+-----------+
では、社員テーブルにレコードを入れるときに、部署テーブルに存在しない部署IDを入れてみます。
INSERT INTO employees (name, department_id) VALUES
('田中', 3),
('山田', 2),
('佐藤', 3),
('鈴木', 5); -- 部署テーブルに存在しない部署ID
外部キー制約の違反に関するエラーが出て、レコードを入れられませんでした。
外部キー制約が機能しているのが分かります。
次に、部署テーブルの部署IDに関するデータを削除、変更してみます。
DELETE FROM departments WHERE id = 3;
UPDATE departments SET id = 100 WHERE id = 1;
同様に外部キー制約の違反に関するエラーが出て、削除や変更ができません。
テーブル自体の削除も、同様に外部キー制約の違反で不可です。
もしテーブルを削除したい場合は、子テーブルの社員テーブルを削除した後であれば、親テーブルの部署テーブルを削除することができます。
DROP TABLE employees;
DROP TABLE departments;
4. 親テーブルの変更を子テーブルにも反映させる
部署テーブルの該当レコードが削除されたり変更があった場合に、社員テーブルの部署IDカラムにも変更が適切に反映されるように設定してみます。
いったん社員テーブルは削除して、テーブル定義をし直します。
DROP TABLE employees;
社員テーブルを定義し直します。
CREATE TABLE employees (
PRIMARY KEY (id), -- 主キーの設定
id INT AUTO_INCREMENT, -- 社員ID
name VARCHAR(20), -- 社員名
department_id INT, -- 部署ID
FOREIGN KEY (department_id) REFERENCES departments(id) -- 外部キーの設定
ON DELETE CASCADE -- ここを追加！！
ON UPDATE CASCADE -- ここを追加！！
);
ON DELETE CASCADE は、親テーブルの該当データの削除に合わせて、子テーブルの該当データも削除されるようにするオプションです。
ON UPDATE CASCADE は、親テーブルの該当データの変更に合わせて、子テーブルの該当データも変更されるようにするオプションです。
動作を確認するため、作った社員テーブルにレコードを入れておきます。
INSERT INTO employees (name, department_id) VALUES
('田中', 3),
('山田', 2),
('佐藤', 3),
('鈴木', 1);
では、部署テーブルの部署IDを変更してみます。
UPDATE departments SET id = 100 WHERE id = 1;
エラーなく実行できました。社員テーブルを確認してみます。
SELECT * FROM employees;
出力結果：
社員テーブルの部署IDも 100 に更新されています。
+----+--------+---------------+
| id | name | department_id |
+----+--------+---------------+
| 1 | 田中 | 3 |
| 2 | 山田 | 2 |
| 3 | 佐藤 | 3 |
| 4 | 鈴木 | 100 |
+----+--------+---------------+
部署テーブルからレコードの削除もしてみます。
DELETE FROM departments WHERE id = 3;
社員テーブルを確認してみます。
SELECT * FROM employees;
出力結果：
部署IDが 3 のレコードが連動して削除されました。
+----+--------+---------------+
| id | name | department_id |
+----+--------+---------------+
| 2 | 山田 | 2 |
| 4 | 鈴木 | 100 |
+----+--------+---------------+
以上となります。
外部キーを適切に使って、整合性のとれたテーブル設計をしていきたいです。