バージョン 3.23.43b より、InnoDB に外部キー制約が装備されるようになりました。 InnoDB は、データの完全性を守るためにユーザが外部キー制約を定義できるようにした最初の MySQL テーブル型です。

InnoDB における外部キー制約定義の構文は次のとおりです。

[CONSTRAINT [symbol]] FOREIGN KEY (index_col_name, ...)
                  REFERENCES table_name (index_col_name, ...)
                  [ON DELETE {CASCADE | SET NULL | NO ACTION
                              | RESTRICT}]
                  [ON UPDATE {CASCADE | SET NULL | NO ACTION
                              | RESTRICT}]

どちらのテーブルも InnoDB 型でなければなりません。テーブル内には、外部キーカラムが最初のカラムとして同じ順序で列挙されているインデックスが必要です。また、参照テーブル内には、参照カラムが最初のカラムとして同じ順序で列挙されているインデックスが必要です。InnoDB は、外部キーまたは参照キーに対して自動的にインデックスを作成しません。したがって、ユーザが明示的にインデックスを作成する必要があります。外部キーのチェックを高速化し、テーブルスキャンを不要にするには、インデックスが必要です。

外部キーと参照キーの対応するカラムは、型を変換しなくても比較できるように、InnoDB 内部で同じデータ型にする必要があります。 整数型については、サイズと符号の有無が同じでなければなりません。 文字列型の長さは同じでなくてもかまいません。 SET NULL アクションを指定する場合は、子テーブル内のカラムを NOT NULL と宣言していないことを確認してください。

MySQL が CREATE TABLE ステートメントでエラー番号 1005 を返し、エラーメッセージ文字列に errno 150 が示されている場合は、外部キー制約が正しく作成されなかったためにテーブルの作成が失敗しています。 同様に、ALTER TABLE が失敗して errno 150 が示された場合は、変更されたテーブルに対して外部キー定義が誤って作成されています。バージョン 4.0.13 より、SHOW INNODB STATUS を使用して、サーバで最後に発生した InnoDB 外部キーエラーの詳細な説明を参照できるようになりました。

バージョン 3.23.50 より、InnoDB は NULL カラムを含んでいる外部キーまたは参照キー値で外部キー制約をチェックしなくなりました。

標準 SQL からの逸脱: 親テーブルに同じ参照キー値を持つ複数のレコードがある場合、InnoDB の外部キーチェックでは、同じキー値を持つ親レコードが他に存在しないものとして処理が行われます。たとえば、RESTRICT 型制約を定義し、かつ複数の親レコードを持つ子レコードが存在する場合、InnoDB はこれらの親レコードの削除を禁止します。

バージョン 3.23.50 より、ON DELETE CASCADE 節または ON DELETE SET NULL 節を外部キー制約に付けることもできるようになりました。対応する ON UPDATE オプションは、4.0.8 より利用可能です。ON DELETE CASCADE が指定されている場合に親テーブル内のレコードが削除されると、InnoDB は子テーブル内で親レコード内の参照キー値と等しい外部キー値を持つすべてのレコードを自動的に削除します。ON DELETE SET NULL が指定されている場合は、子レコードが自動的に更新されて、外部キー内のカラムが SQL の NULL 値に設定されます。

標準 SQL からの逸脱: ON UPDATE CASCADE または ON UPDATE SET NULL は、カスケード中にすでに更新したテーブルを繰り返して更新する場合に、RESTRICT のように動作します。これは、カスケードされた更新から生じる無限ループを防ぐためです。一方、自己参照型の ON DELETE SET NULL が 4.0.13 から動作するようになりました。 自己参照型の ON DELETE CASCADE は、以前から動作していました。

次に例を示します。

CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
             FOREIGN KEY (parent_id) REFERENCES parent(id)
             ON DELETE SET NULL
) TYPE=INNODB;

次に示すのは複雑な例です。

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                      PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                      product_category INT NOT NULL,
                      product_id INT NOT NULL,
                      customer_id INT NOT NULL,
                      PRIMARY KEY(no),
                      INDEX (product_category, product_id),
                      FOREIGN KEY (product_category, product_id)
                        REFERENCES product(category, id)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
                      INDEX (customer_id),
                      FOREIGN KEY (customer_id)
                        REFERENCES customer(id)) TYPE=INNODB;

バージョン 3.23.50 より、InnoDB では次のステートメントによって新しい外部キー制約をテーブルに追加できるようになりました。

ALTER TABLE yourtablename
ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES anothertablename(...)
[on_delete_and_on_update_actions]

ただし、必要なインデックスを先に作成することを忘れないでください。

バージョン 4.0.13 より、InnoDB が次のステートメントをサポートするようになりました。

ALTER TABLE yourtablename DROP FOREIGN KEY internally_generated_foreign_key_id

外部キーを破棄する場合は、SHOW CREATE TABLE を使って、内部で生成された外部キー ID を確認する必要があります。

InnoDB が 3.23.50 より前のバージョンである場合は、外部キー制約を持つテーブルまたは外部キー制約で参照されるテーブルに関連して ALTER TABLE または CREATE INDEX を使用しないでください。ALTER TABLE を実行すると、テーブルに定義されているすべての外部キー制約が削除されます。ALTER TABLE は、参照テーブルにも使用しないでください。ただし、スキーマを変更する場合は DROP TABLE および CREATE TABLE を使用します。MySQL は、ALTER TABLE を実行するときに内部的に RENAME TABLE を使用する場合があります。この場合、テーブルを参照する外部キー制約で混乱が生じます。 CREATE INDEX ステートメントは、MySQL では ALTER TABLE として処理されるため、このステートメントにもこれらの制約が適用されます。

InnoDB は、外部キーチェックを実行する際に、参照する子レコードまたは親レコードに対して共有行レベルロックを設定します。 InnoDB は、外部キー制約を即座にチェックします。チェックがトランザクションコミットまで延期されることはありません。

外部キー制約を、たとえば LOAD DATA 処理の間だけ無視する場合は、SET FOREIGN_KEY_CHECKS=0 を実行します。

InnoDB では、外部キー制約によって参照されているテーブルでも破棄できます。この場合、その制約が壊れることになります。テーブルを破棄すると、その作成ステートメントで定義された制約も破棄されます。

破棄されたテーブルを再作成する場合は、そのテーブルを参照する外部キー制約に沿った定義をテーブル内に設定する必要があります。すでに説明したように、このテーブルには、正しい名前と型を持つカラム、および参照キー上のインデックスが必要です。 これらの条件が満たされていないと、MySQL からエラー番号 1005 が返され、エラーメッセージ文字列に errno 150 が示されます。

バージョン 3.23.50 より、次のステートメントを呼び出すと、InnoDB からテーブルの外部キー定義が返されるようになりました。

SHOW CREATE TABLE yourtablename

また、mysqldump によってテーブルの正しい定義と共に外部キーがダンプファイルに出力されます。

また、次のステートメントでテーブル T の外部キー制約も列挙できます。

SHOW TABLE STATUS FROM yourdatabasename LIKE 'T'

外部キー制約は、出力のテーブルコメントに列挙されます。