MySQL - 外部キー

概要

外部キーは、データベースにおいてテーブル間の関連性を定義し、参照整合性を維持するための制約である。

MySQLでは、InnoDBストレージエンジンが外部キー制約をサポートしており、親テーブルと子テーブル間のデータの一貫性を自動的に保証する。
外部キーを使用することにより、存在しないレコードへの参照や関連データの不整合を防ぐことができる。

外部キーの基本構文

外部キーは、FOREIGN KEY 句を使用してテーブル作成時またはテーブル変更時に定義する。

 # テーブル作成時に外部キーを定義
 
 CREATE TABLE 子テーブル名 (
    カラム名 データ型,
    FOREIGN KEY (カラム名) REFERENCES 親テーブル名(参照カラム名)
 );
 
 # 既存テーブルに外部キーを追加
 ALTER TABLE 子テーブル名
 ADD CONSTRAINT 制約名
 FOREIGN KEY (カラム名) REFERENCES 親テーブル名(参照カラム名);

外部キーの制約

MySQLの外部キー制約で指定できる参照アクションを以下に示す。

MySQLで実質的に使用できるものは、CASCADE、SET NULL、RESTRICT、NO ACTIONの4つである。
SET DEFAULTは構文上は認識されるが、InnoDBでは使用できない。

MySQLで指定可能な全アクション

アクション	動作	備考
CASCADE	親レコードの削除・更新時に、子レコードも同様に削除・更新される。
SET NULL	親レコードの削除・更新時に、子レコードの外部キー列がNULLに設定される。	外部キー列がNOT NULL制約を持つ場合は使用できない。
RESTRICT	参照している子レコードが存在する場合、親レコードの削除・更新を即座に拒否する	エラーが発生し、操作は実行されない。
NO ACTION	MySQLではRESTRICTと同じ動作をする	SQL標準ではチェックのタイミングが異なる場合があるが、MySQLでは実質的に同一である。
SET DEFAULT	親レコードの削除・更新時に、子レコードの外部キー列がデフォルト値に設定される	MySQL 8.0.13以降でサポートされたが、InnoDBストレージエンジンでは認識されるものの拒否される。 (実際には使用できない)

※注意
アクションを指定しない場合、デフォルトは RESTRICT である。

 # 使用例
 
 CREATE TABLE orders (
    id INT PRIMARY KEY,              -- 注文ID（主キー）
    customer_id INT,                 -- 顧客ID（外部キー）
    FOREIGN KEY (customer_id)        -- 外部キー制約の定義
       REFERENCES customers(id)      -- customersテーブルのidカラムを参照
       ON DELETE CASCADE             -- 親レコード削除時、この注文も削除
       ON UPDATE RESTRICT            -- 親レコード更新時、子レコードがあれば拒否
 );

外部キーの要件

MySQLで外部キーを使用するには、以下に示す要件を満たす必要がある。

• 両方のテーブルがInnoDBストレージエンジンを使用していること。
• 外部キー列と参照先の列は、同じデータ型でなければならない。
• 参照先の列は、インデックスが作成されている必要がある。(通常は、主キー または ユニークキー)
• 外部キー列にもインデックスが自動的に作成される、

外部キーの確認

定義されている外部キー制約を確認するには、以下に示すコマンドを使用する。

 # テーブルの外部キー情報を表示
 SHOW CREATE TABLE テーブル名;
 
 # データベース内の外部キー制約を確認
 SELECT * FROM information_schema.KEY_COLUMN_USAGE
    WHERE TABLE_SCHEMA = 'データベース名'
       AND REFERENCED_TABLE_NAME IS NOT NULL;

外部キーの削除

既存の外部キー制約を削除するには、ALTER TABLE文を使用する。

 # 外部キー制約の削除
 
 ALTER TABLE テーブル名
    DROP FOREIGN KEY 制約名;

※注意
制約名を確認するには、SHOW CREATE TABLEコマンドを使用する。

外部キー制約の一時的な無効化

データの一括投入などで外部キーチェックを一時的に無効にする場合は、以下に示すコマンドを使用する。

 # 外部キーチェックを無効化
 
 SET FOREIGN_KEY_CHECKS = 0;
 
 # データ操作を実行
 # ...略
 
 # 外部キーチェックを再度有効化
 SET FOREIGN_KEY_CHECKS = 1;

※警告
外部キーチェックを無効にすると、データの整合性が失われる可能性があるため、使用には十分注意が必要である。