MySQLではデータベースの一貫性を保つため、外部キー（Foreign Key）制約がサポートされています。外部キーとは、2つのテーブル（親テーブルと子テーブル）間における参照整合性を保つために設定される制約です。InnoDBとNDBストレージエンジンのみでサポートされています。今回はInnoDBにおける外部キー制約について紹介します。

外部キーの作成

はじめに、外部キーはCREATE TABLEやALTER TABLE構文で作成できます。例としてt0テーブルを親テーブル、t1テーブルを子テーブルでuser_idカラムを外部キーとして設定してみます。下記、t1テーブルのCREATE構文のCONSTRAINT以下が外部キー作成のための構文になります。

mysql> CREATE TABLE t0 (
 user_id int primary key
) ENGINE=INNODB;

mysql> CREATE TABLE t1 (
 id int primary key ,
 user_id int ,
 update_time datetime ,
 KEY idx_userid (user_id),
 CONSTRAINT fk_t0_userid 
   FOREIGN KEY fk_userid(user_id) 
   REFERENCES t0 (user_id)
   ON DELETE RESTRICT ON UPDATE RESTRICT
 ) ENGINE=INNODB;

• CONSTRAINT fk_t0_useridは制約名です。ここを省略した場合は自動で制約名が作成されます。この制約名はデータベース単位で一意の値になります。
• FOREIGN KEY fk_userid(user_id)は、外部キーIDと外部キーとなるカラムを指定します。
• REFERENCES t0 (user_id)は、参照される親テーブルとカラムを指定します。
• ON DELETE RESTRICT ON UPDATE RESTRICTは、参照アクションです。

参照アクション

参照アクションは、親テーブルの参照されるキーに対してUPDATE、またはDELETEした場合の子テーブルの外部キーに与える影響を、以下のアクションから選択できます。

外部キー作成の条件

外部キーを作成するにはいくつかの条件があります。

1. 親テーブルと子テーブルは同じストレージエンジンでなければならない
2. 親テーブルの参照されるキーと子テーブルの外部キーは、それぞれ同様のデータ型でなければならない
3. 親テーブルの参照されるキーと子テーブルの外部キーにはインデックスが必要
4. InnoDBのパーティションテーブルは、外部キーがサポートされていない

3．については、補足として以下があります。

子テーブルにインデックスが存在しない場合、外部キー作成と同時に暗黙でインデックスが作成される

その時のインデックス名は、以下のルールによって決まります。

1. 制約名
2. 1が記述がない場合、外部キーID
3. 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テーブルからインデックス作成部分を除外して実行します。

mysql> CREATE TABLE t1 (
         id int primary key ,
         user_id int ,
         update_time datetime ,
         CONSTRAINT fk_t0_userid 
            FOREIGN KEY fk_userid(user_id) 
            REFERENCES t0 (user_id)
       ) ENGINE=INNODB;

SHOW CREATE TABLE構文で確認します。

mysql> SHOW CREATE TABLE t1\G
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_t0_userid` (`user_id`),
  CONSTRAINT `fk_t0_userid` FOREIGN KEY (`user_id`) REFERENCES `t0` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

KEY fk_t0_userid (user_id)という前述のルールにより制約名でインデックスが作成されました。

ここに(user_id,update_time)で類似のインデックスを作成して確認します。

mysql> ALTER TABLE t1 ADD KEY idx_userid_updatetime(user_id,update_time);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t1\G
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userid_updatetime` (`user_id`,`update_time`),
  CONSTRAINT `fk_t0_userid` FOREIGN KEY (`user_id`) REFERENCES `t0` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

fk_t0_useridのインデックスが削除されました。ここでの注意として、以下が挙げられます。

USEまたはFORCE INDEXを使用したクエリ

既存のクエリにUSEまたはFORCE INDEXで先ほど暗黙で作成されたインデックスを指定していた場合、対象のインデックスが存在していないためエラーとなってしまいます。

mysql> SELECT * FROM t1 USE INDEX (fk_t0_userid);
ERROR 1176 (42000): Key 'fk_t0_userid' doesn't exist in table 't1'

MySQL5.7.20現在、暗黙または明示的に作成されたインデックスかを確認する手段はありません。判断する方法としては、暗黙で追加されたインデックスは明示的に同じインデックス名で再作成できます。明示的に作成されたインデックスであればすでに存在しているため、Duplicate key nameでエラーとなります。

mysql> SHOW CREATE TABLE t1\G
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_t0_userid` (`user_id`),
  CONSTRAINT `fk_t0_userid` FOREIGN KEY (`user_id`) REFERENCES `t0` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

mysql> ALTER TABLE t1 ADD KEY `fk_t0_userid` (`user_id`);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

しかし、この方法はmysqldumpによってリストアしたスレーブが存在した場合は、Duplicate key nameのエラーでレプリケーションが停止してしまう恐れがあります。なぜなら、mysqldumpでエクスポートしたDDL（SHOW CREATE TABLE構文と同様）はインデックスの記述が追加されているため、リストア時に明示的にインデックスが作成されてしまうためです。

mysql> SHOW SLAVE STATUS¥G

Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1061
Last_Error: Error 'Duplicate key name 'fk_t0_userid'' on query. Default database: 't'. Query: 'ALTER TABLE t1 ADD KEY `fk_t0_userid` (`user_id`)'

結論としては、以下の対策ができると思います。

• 外部キー作成する際は予期せぬエラーを防ぐために明示的にインデックスを作成する
• インデックスの命名規則を定めるようにして、暗黙で作成されたインデックスは命名規則と異なるので気づけるようにする