外部キーとは？〜概要から変数や処理の書き方を解説〜

2021.10.05 株式会社システムインテグレータ

外部キーを忘れずに設定することで、各テーブル間のデータの関連性を明らかにし、データの更新・削除時に整合性を保つことができるようになります。
ここでは外部キーの概要と作成方法をご紹介します。

外部キーとは？

外部キーとは「親テーブルに存在しないデータを子テーブルが持つことが無いようにするための制約」です。
例えば部署テーブルと従業員テーブルがあり、従業員テーブルに部署IDがあった場合、存在しない部署IDで従業員テーブルを登録できないようになります。

外部キーの役割

外部キーを作成することによって基本的にはこの3つの制約を実現できます。

1. 存在しない値を外部キーに設定できなくなる
2. 親テーブルに存在する子テーブルの外部キーで繋がれたデータを削除できなくなる
3. 親テーブルのレコードを削除したときに外部キーで紐づいている子テーブルのレコードも削除する

一つずつ見ていきましょう。

存在しない値を外部キーに設定できなくなる

子テーブルに設定した外部キーの
実際に試してみましょう。今回はこちらのテーブルを使います。

EMPLOYEES
外部キーとは？〜概要から変数や処理の書き方を解説〜 1

DEPARTMENT
外部キーとは？〜概要から変数や処理の書き方を解説〜 2

BRANCH
外部キーとは？〜概要から変数や処理の書き方を解説〜 3

DEPARTMENTテーブルのIDとEMPLOYEESテーブルのDEPARTMENT_IDは外部キーで結ばれています。

この場合親テーブルはDEPARTMENTテーブル、子テーブルはEMPLOYEESテーブルになります。ではDEPARTMENTテーブルのIDに存在しないDEPARTMENT_IDでEMPLOYEESテーブルのレコードを作成してみましょう。

コード：
INSERT INTO EMPLOYEES VALUES (3, '遠藤', 99, 0)

結果：
外部キーとは？〜概要から変数や処理の書き方を解説〜 4

「parent key not found」と記載されている通り、外部キーとして紐づいている親テーブルの列に存在しないIDを指定したことでエラーになっているのが分かります。このように、外部キーを設定することで親テーブルに存在しないキーを使って子テーブルのレコードを作成できなくなります。

なお、ここでは登録のみ検証しましたが、更新しようとした場合も同じエラーになります。

親テーブルに存在する子テーブルの外部キーで繋がれたデータを削除できなくなる

既に子テーブルで使用されている（参照されている）親テーブルのキーは削除できなくなります。
これも検証してみましょう。
先ほどのDEPARTMENTテーブルからデータを削除してみます。

コード：
DELETE FROM DEPARTMENT WHERE ID = 1

結果：外部キーとは？〜概要から変数や処理の書き方を解説〜 5

今度は「child record found」とあるので、既に子テーブルに参照されているレコードを削除してエラーになっているのが分かります。

親テーブルのレコードを削除したときに外部キーで紐づいている子テーブルのレコードも削除する

ON DELETE CASCADE句を付けて外部キーをテーブルに付与することで、親テーブルのレコードが削除されたときに同時に紐づいている子レコードを削除するようにできます。（外部キーの作成方法については後述します）

ON DELETE CASCADEを付与した状態で先ほど同様DEPARTMENTテーブルのレコードを削除してみましょう。

EMPLOYEES
外部キーとは？〜概要から変数や処理の書き方を解説〜 6

DEPARTMENT
外部キーとは？〜概要から変数や処理の書き方を解説〜 7

コード：
DELETE FROM DEPARTMENT WHERE ID = 1

結果：
外部キーとは？〜概要から変数や処理の書き方を解説〜 8

IDが1のレコードを削除するので、同時に田中さんのレコードが削除されます。

外部キーの作成方法

実際に外部キー制約を列に付与する方法を解説します。
外部キー制約は主キー制約と同じく「新規テーブル作成時に付与する」「作成済みのテーブルに後から付与」の2パターンがあります。
新規テーブル作成時に付与する
新規テーブルの以下の文を追加することで、テーブル作成時に外部キー制約を付与できます。

外部キー制約文：
CONSTRAINT 制約名 FOREIGN KEY (列名) REFERENCES 親テーブル名 (親テーブルの列名)

実際に例を見てみましょう。DEPARTMENTテーブルのIDを親テーブル列として、EMPLOYEESテーブルのDEPARTMENT_IDに外部キー制約を付与します。

コード：
CREATE TABLE EMPLOYEES (
　ID NUMBER PRIMARY KEY,
　NAME VARCHAR2(50) NOT NULL,
　DEPARTMENT_ID NUMBER,
　BRANCH_ID NUMBER,
　CONSTRAINT FK_EMPLOYEES_DEPARTMENT_ID FOREIGN KEY (DEPARTMENT_ID)
　REFERENCES DEPARTMENT (ID)
);

これでDEPARTMENT_IDに外部キー制約が付与されました。実際にDEPARTMENTテーブルのIDに存在しない値でレコードを作成しようとするとエラーになります。
また、ON DELETE CASCADE句を追加して外部キー制約の挙動を変えたい場合は以下のような文にすると良いです。

外部キー制約文(ON DELETE CASCADE)：
CONSTRAINT 制約名 FOREIGN KEY (列名) REFERENCES 親テーブル名 (親テーブルの列名) ON DELETE CASCADE

作成済みのテーブルに値から付与する

以下の文を実行することで、作成済みのテーブルに外部キー制約を付与できます。

外部キー制約文：
ALTER TABLE EMPLOYEES ADD CONSTRAINT 外部キー制約名 FOREIGN KEY (列名) REFERENCES 親テーブル名 (親テーブルの列名)

DEPARTMENT_IDに制約を付与する場合はこうなります。

コード：
ALTER TABLE EMPLOYEES ADD CONSTRAINT FK FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT (ID)

外部キー制約を削除する

外部キー制約を削除する場合は以下のSQLを実行します。

外部キー削除文：
ALTER TABLE テーブル名 DROP CONSTRAINT 外部キー制約名

外部キー制約名には外部キー付与時に設定した制約名を指定します。
また、外部キーは子テーブルに付与するので、テーブル名には子テーブル名が入ります。

コード：
ALTER TABLE EMPLOYEES DROP CONSTRAINT FK_EMPLOYEES_DEPARTMENT_ID

外部キー制約名を調べたい場合は以下のSQLを実行します。

コード：
SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = ‘EMPLOYEES’
AND CONSTRAINT_TYPE = 'R' --Rは外部キー制約

結果：
外部キーとは？〜概要から変数や処理の書き方を解説〜 9

外部キー制約を一時的に無効化する

ALTER TABLE DISABLE CONSTRAINT句で外部キー制約を一時的に無効化することもできます。

コード：
ALTER TABLE EMPLOYEES DISABLE CONSTRAINT FK_EMPLOYEES_DEPARTMENT_ID

また、再度有効化したい場合はDISABLEの箇所をENABLEに変更します。

コード：
ALTER TABLE EMPLOYEES ENABLE CONSTRAINT FK_EMPLOYEES_DEPARTMENT_ID

制約が有効か無効を調べたい場合はUSER_CONSTRAINTSテーブルのSTATUS列を参照してください。ENABLEDなら有効、DISABLEDなら無効になっています。