InnoDB
ストレージエンジンは、CASCADE
、ON DELETE
、ON UPDATE
などの外部キー制約のチェックをサポートします。セクション14.6.6「InnoDB と FOREIGN KEY 制約」を参照してください。
InnoDB
および NDB
以外のストレージエンジンの場合、MySQL Server は CREATE TABLE
ステートメントの FOREIGN KEY
構文を解析しますが、これを使用したり格納したりしません。この情報は、mysqldump にも存在し、Connector/ODBC を使用して取得できます。INFORMATION_SCHEMA
情報データベース内の INFORMATION_SCHEMA.TABLE_CONSTRAINTS
テーブルをチェックすることによって、どのテーブルに外部キー制約があるかを確認できます。INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
テーブルから、外部キーに関する詳細を取得できます。さらに、InnoDB
には、InnoDB
テーブル上の外部キーに関する情報を含む多数の INFORMATION_SCHEMA
テーブルが用意されています。セクション21.29「InnoDB の INFORMATION_SCHEMA テーブル」を参照してください。
データベース開発者にとって、次のような外部キーを使用するメリットがあります。
関係が適切に設計されている場合、外部キー制約によって、プログラマがデータベースで不整合を引き起こすことが少なくなります。
データベースサーバーによって集中的に制約チェックが行われるため、アプリケーション側でのこれらのチェックが不要になります。このことで、さまざまなアプリケーションで制約が同じようにチェックされない場合があるという可能性がなくなります。
連鎖更新および削除を使用すると、アプリケーションコードを単純化することができます。
適切に設計された外部キールールは、テーブル間の関係の記述に役立ちます。
SQL の外部キーは、テーブルの結合ではなく、参照整合性のチェックと実施に使用されます。SELECT
ステートメントで複数のテーブルからの結果を取得する場合は、次のようにそれらの結合を実行してください。
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
セクション13.2.9.2「JOIN 構文」およびセクション3.6.6「外部キーの使用」を参照してください。
ON DELETE ...
を使用しない FOREIGN KEY
構文は、自動的に WHERE
句を作成するために、ODBC アプリケーションで使用されることがよくあります。
SQL 標準からの逸脱
MySQL での外部キーの実装は、次の重要な点で SQL 標準と異なります。
-
同じ参照キー値を持つ複数の行が親テーブルにある場合、
InnoDB
は、同じキー値を持つほかの親の行が存在しないかのように、外部キーチェックで動作します。たとえば、RESTRICT
型の制約が定義されていて、複数の親の行を含む子の行が存在する場合は、これらの親の行のいずれかを削除することがInnoDB
で許可されません。InnoDB
では、外部キー制約に対応するインデックス内のレコードに基づいて、深さ優先アルゴリズムを使用したカスケード操作が実行されます。 UNIQUE
でないキーを参照するFOREIGN KEY
制約は、標準 SQL ではなくInnoDB
の拡張機能です。ON UPDATE CASCADE
またはON UPDATE SET NULL
が同じカスケード中に以前に更新していた同じテーブルを更新するように再帰する場合、RESTRICT
のように機能します。つまり、自己参照型ON UPDATE CASCADE
またはON UPDATE SET NULL
操作は使用できません。この目的は、カスケード更新で発生する無限ループを回避することです。反対に、自己参照型ON DELETE SET NULL
は、自己参照型ON DELETE CASCADE
と同様に動作できます。カスケード操作は、15 レベルよりも深くネストされる可能性がありません。多くの行を挿入、削除、または更新する SQL ステートメントでは、外部キー制約 (一意の制約など) が行ごとにチェックされます。外部キーチェックを行なっているとき、
InnoDB
は、調べる必要のある子レコードまたは親レコード上に共有行レベルロックを設定します。MySQL は外部キー制約の確認を即座に行います。その確認がトランザクションコミットまで遅延されることはありません。SQL 標準によると、デフォルトの動作は遅延チェックにするべきです。つまり、SQL ステートメント全体が処理されたあとにはじめて、制約がチェックされます。これは、外部キーを使用して自己参照する行を削除できないことを意味します。
InnoDB
外部キーが SQL 標準とどのように異なるかについては、セクション14.6.6「InnoDB と FOREIGN KEY 制約」を参照してください。