こんにちは！システムエンジニアのオオイシです。
SQLの外部キー(FOREIGN KEY)をご存知ですか。外部キーの使いかたを覚えると、関連するテーブル間の整合性をデータベースに保証させることが可能です。
この記事では、
といった、基本的な解説から
- 外部キーは、親テーブルに存在しない値の登録をエラーにする
- 外部キーは、子テーブルに存在する値の削除をエラーにする
- 外部キーを後から追加する方法
- 外部キーを削除する方法
などの応用的な使い方関しても解説していきます。
今回はそんな外部キーの使い方をわかりやすく解説します！
外部キーとは？
外部キー(FOREIGN KEY)とは、関連したテーブル間を結ぶために設定する列のことで、データの整合性をデータベースに保証させるために利用します。
例えば、従業員テーブルに「従業員番号、部署番号、姓、名」の項目があるとして、部署テーブルの「部署番号」関連づけしたい場合に外部キーを利用します。
外部キーを設定して参照する側は子テーブル、設定元は親テーブルと言います。
そんな外部キーの役割について次項で説明していきます。なお、主キー(PRIMARY KEY)については、こちらで詳しく解説していますので、ぜひ参考にしてください！
外部キーの役割について理解しよう
外部キーは、関連するテーブル間の整合性を保ちたい列に設定するデータベースの重要な機能です。部署テーブルと従業員デーブルを例に説明すると、
- 部署テーブルに存在しない部署コードは、従業員テーブルに登録させない
- 従業員テーブルに登録のある値は、部署テーブルから削除させない
などの便利な制約を設定できます。
外部キーの役割について理解いただけたところで、次は外部キーの作成方法について解説していきます！
外部キーを作成してみよう
外部キーを持つテーブルを作成するには、
- FOREIGN KEY(列名) REFERENCES 親テーブル名(親列名)
をCREATE TABLEの最後に追加します。
従業員テーブルの部署番号(department_no)に、外部キーを作成するサンプルコードを確認して見ましょう。はじめに親テーブルの部署テーブルを作成します。
CREATE TABLE sample1_departments (
department_no char(5), -- 部署番号
department_name varchar(255), -- 部署名
PRIMARY KEY (department_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- テストデータ
INSERT INTO sample1_departments VALUES('A0001', 'アプリ開発部');
INSERT INTO sample1_departments VALUES('A0002', 'データベース開発部');
INSERT INTO sample1_departments VALUES('B0003', 'Webデザイン部');
つづいて、従業員テーブルを作成するとともに、FOREIGN KEYで「部署番号(department_no)」に外部キーを設定します。
CREATE TABLE sample1_employees (
no int NOT NULL, -- 従業員番号
department_no char(5), -- 部署番号
last_name varchar(255), -- 名
first_name varchar(255), -- 姓
PRIMARY KEY (no), -- 主キー
FOREIGN KEY(department_no) -- 外部キー
REFERENCES sample1_departments(department_no) -- 部署テーブル.部署番号
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- テストデータ
INSERT INTO sample1_employees VALUES('10001', 'A0001','侍','太郎');
INSERT INTO sample1_employees VALUES('10002', 'A0002','侍','次郎');
INSERT INTO sample1_employees VALUES('10003', 'B0003','侍','花子');
以上で、従業員テーブルの「部署番号(department_no)」に外部キーを設定することができました。
次項では、外部キー設定によって保証された整合性について確認していきましょう。
外部キーは、親テーブルに存在しない値の登録をエラーにする
外部キーを設定すると、親テーブルに存在しない値を登録した場合にはエラーになります。
次のサンプルコードで確認してみましょう。
INSERT INTO sample1_employees VALUES('10004', 'C0004','侍','三郎');
実行結果：
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`employees`.`sample1_employees`, CONSTRAINT `sample1_employees_ibfk_1` FOREIGN KEY (`department_no`) REFERENCES `sample1_departments` (`department_no`))
このように、従業員テーブル(子テーブル)に登録するとエラーになることが確認できました。
次項では、親テーブルのレコード(データ行)を削除した場合について解説していきます。
外部キーは、子テーブルに存在する値の削除をエラーにする
外部キーを設定すると、子テーブルに値が存在する、親テーブルのレコードを削除しようとするとエラーとなるため削除できません。
次のサンプルコードで確認してみましょう。
DELETE FROM sample1_departments WHERE department_no = 'A0001';
実行結果：
ERROR 1451 (23000):Cannot delete or update a parent row: a foreign key constraint fails (`employees`.`sample1_employees`, CONSTRAINT `sample1_employees_ibfk_1` FOREIGN KEY (`department_no`) REFERENCES `sample1_departments` (`department_no`))
このように、部署テーブル(親レコード)のレコードはエラーになるため削除できないことが確認できました。
もしも、データを削除したい場合は、従業員テーブル(子テーブル)のデータ削除する必要があります。次項では、外部キーを後から追加する方法について解説していきます！
外部キーを後から追加する方法
存在するテーブルに後から外部キーを設定するには、次のコマンドで作成できます。
- ALTER TABLE 子テーブル名 ADD FOREIGN KEY(子の列名) REFERENCES 親テーブル名(親の列名)
従業員テーブルの「部署番号(department_no)」に外部キーを設定するサンプルコードを確認してみましょう。はじめに外部キーの設定なしで従業員テーブルを作成します。
CREATE TABLE sample2_employees ( no int NOT NULL, -- 従業員番号 department_no char(5), -- 部署番号 last_name varchar(255), -- 名 first_name varchar(255), -- 姓 PRIMARY KEY (no) -- 主キー ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
つづいて、ALTER TABLE文で「部署番号(department_no)」に外部キーを設定します。
ALTER TABLE sample2_employees ADD FOREIGN KEY(department_no) REFERENCES sample1_departments(department_no);
このように、後から外部キーを設定することが可能です。
次項では外部キーの削除方法についてみていきましょう。
外部キーを削除する方法
外部キーを削除するには、
- ALTER TABLE テーブル名 DROP FOREIGN KEY 外部キーのID
で削除できます。
外部キーのIDは、実は外部キーの作成時に自動で命名されるので、名称を調べてから削除します。SHOW CREATE TABLEコマンドを使います。
SHOW CREATE TABLE sample2_employees;
実行結果：
CREATE TABLE `sample2_employees` ( `no` int(11) NOT NULL, `department_no` char(5) DEFAULT NULL, `last_name` varchar(255) DEFAULT NULL, `first_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`no`), KEY `department_no` (`department_no`), CONSTRAINT `sample2_employees_ibfk_1` FOREIGN KEY (`department_no`) REFERENCES `sample1_departments` (`department_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
この場合sample2_employees_ibfk_1が外部キーのIDになります。
削除しましょう。
ALTER TABLE sample2_employees DROP FOREIGN KEY sample2_employees_ibfk_1;
外部キーを確認：
CREATE TABLE `sample2_employees` ( `no` int(11) NOT NULL, `department_no` char(5) DEFAULT NULL, `last_name` varchar(255) DEFAULT NULL, `first_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`no`), KEY `department_no` (`department_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
このように、外部キーを削除することができました。
まとめ
いかかでしたか？今回はSQLの外部キー(FOREIGN KEY)について解説しました。
外部キーは、関連のあるテーブル間の整合性をデータベースに保証させるために設定します。外部キーを使うと、間違ったデータの登録や削除を防いだり、関連するデータを一括に変更できるため、ぜひ活用してみてください。
そして、外部キーの使い方を忘れてしまったらこの記事を確認してくださいね！
