MySQLではデータベースの一貫性を保つため、外部キー(Foreign Key)制約がサポートされています。外部キーとは、2つのテーブル(親テーブルと子テーブル)間における参照整合性を保つために設定される制約です。InnoDBとNDBストレージエンジンのみでサポートされています。今回はInnoDBにおける外部キー制約について紹介します。
外部キーの作成
はじめに、外部キーはCREATE TABLE
やALTER TABLE
構文で作成できます。例としてt0
テーブルを親テーブル、t1
テーブルを子テーブルでuser_id
カラムを外部キーとして設定してみます。下記、t1
テーブルのCREATE構文のCONSTRAINT以下が外部キー作成のための構文になります。
CONSTRAINT fk_t0_userid
は制約名です。ここを省略した場合は自動で制約名が作成されます。この制約名はデータベース単位で一意の値になります。
FOREIGN KEY fk_userid(user_id)
は、外部キーIDと外部キーとなるカラムを指定します。
REFERENCES t0 (user_id)
は、参照される親テーブルとカラムを指定します。
ON DELETE RESTRICT ON UPDATE RESTRICT
は、参照アクションです。
参照アクション
参照アクションは、親テーブルの参照されるキーに対してUPDATE、またはDELETEした場合の子テーブルの外部キーに与える影響を、以下のアクションから選択できます。
アクション | ON UPDATE | ON DELETE |
RESTRICT | 親テーブルへのUPDATEは拒否される(デフォルト) | 親テーブルへのDELETEは拒否される(デフォルト) |
NO ACTION | RESTRICTと同様 | RESTRICTと同様 |
CASCADE | 親テーブルへのUPDATEが実施され、子テーブルの一致する行を自動的にUPDATEされる | 親テーブルへのDELETEが実施され、子テーブルの一致する行を自動的にDELETEされる |
SET NULL | 親テーブルへのUPDATEが実施され、子テーブルの外部キーカラムを NULLに設定 | 親テーブルへのDELETEが実施され、子テーブルの外部キーカラムを NULLに設定 |
外部キー作成の条件
外部キーを作成するにはいくつかの条件があります。
- 親テーブルと子テーブルは同じストレージエンジンでなければならない
- 親テーブルの参照されるキーと子テーブルの外部キーは、それぞれ同様のデータ型でなければならない
- 親テーブルの参照されるキーと子テーブルの外部キーにはインデックスが必要
- InnoDBのパーティションテーブルは、外部キーがサポートされていない
3.については、補足として以下があります。
子テーブルにインデックスが存在しない場合、外部キー作成と同時に暗黙でインデックスが作成される
その時のインデックス名は、以下のルールによって決まります。
- 制約名
- 1が記述がない場合、外部キーID
- 1と2のどちらの記述もない場合、外部キーに指定されたカラム名
インデックスプレフィックスはサポートされない
インデックスプレフィックスとは、インデックスの最大キー長を超えてしまい、データが切り詰められて作成されたインデックスのことです。BLOB型やTEXT型などで起こります。詳しくは第32回 InnoDBインデックスの最大キー長についてを参照ください。
外部キー制約を確認
以下のテーブルから外部キーの関連情報を確認できます。
- INFORMATION_SCHEMA.KEY_COLUMN_USAGE
- INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
- INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS
また、SHOW ENGINE INNODB STATUS
のLATEST FOREIGN KEY ERROR
セクションから最新の外部キー制約エラーに関する情報を確認することができます。
暗黙で作成されたインデックスにおける注意点
前述にあるように、子テーブルにインデックスが存在しない場合は、外部キー作成と同時に暗黙でインデックスが作成されます。
しかし、明示的に類似インデックス(外部キー制約に使用できる別のインデックス)を作成すると、暗黙で作成されたインデックスは暗黙で削除されます。
まずは、どのように暗黙で作成と削除されるか見てみましょう。先ほどの例のt1
テーブルからインデックス作成部分を除外して実行します。
SHOW CREATE TABLE
構文で確認します。
KEY fk_t0_userid (user_id)
という前述のルールにより制約名でインデックスが作成されました。
ここに(user_id,update_time)で類似のインデックスを作成して確認します。
fk_t0_userid
のインデックスが削除されました。ここでの注意として、以下が挙げられます。
USEまたはFORCE INDEXを使用したクエリ
既存のクエリにUSEまたはFORCE INDEXで先ほど暗黙で作成されたインデックスを指定していた場合、対象のインデックスが存在していないためエラーとなってしまいます。
MySQL5.7.20現在、暗黙または明示的に作成されたインデックスかを確認する手段はありません。判断する方法としては、暗黙で追加されたインデックスは明示的に同じインデックス名で再作成できます。明示的に作成されたインデックスであればすでに存在しているため、Duplicate key nameでエラーとなります。
しかし、この方法はmysqldumpによってリストアしたスレーブが存在した場合は、Duplicate key nameのエラーでレプリケーションが停止してしまう恐れがあります。なぜなら、mysqldumpでエクスポートしたDDL(SHOW CREATE TABLE
構文と同様)はインデックスの記述が追加されているため、リストア時に明示的にインデックスが作成されてしまうためです。
結論としては、以下の対策ができると思います。
- 外部キー作成する際は予期せぬエラーを防ぐために明示的にインデックスを作成する
- インデックスの命名規則を定めるようにして、暗黙で作成されたインデックスは命名規則と異なるので気づけるようにする