1.8.5.5 外部キー

1.8.5.5. 外部キー

MySQL サーバ 3.23.44 以降では、CASCADE、ON DELETE、および ON UPDATE を含む外部キー制約のチェックが InnoDBストレージエンジンでサポートされています。.項13.5.6.4. 「FOREIGN KEY 制約」を参照してください。

InnoDB以外のストレージエンジンについては、MySQL サーバでは現在、CREATE TABLE ステートメントで FOREIGN KEY構文のみが解析されますが、この情報は使用/保存されません。近いうちに、この情報がテーブル仕様ファイルに保存され、mysqldump および ODBC によって取得できるように、この実装を拡張する予定です。さらにその後には、MyISAMテーブルについても外部キー制約を実装する予定です。

データベース開発者にとって、外部キーを使用するメリットは以下のとおりです。

関係が適切に設計されている場合、外部キー制約によって、プログラマがデータベースで不整合を引き起こすことが少なくなります。
データベースサーバによって集中的に制約チェックが行われるため、アプリケーション側でのこれらのチェックが不要になります。同様にこのことで、各アプリケーションで制約がすべてチェックされないといった可能性がなくなります。
連鎖更新および削除を使用すると、アプリケーションコードを単純化することができます。
適切に設計された外部キールールは、テーブル間の関係の記述に役立ちます。

これらのメリットには、必要なチェックを行なうため、データベースサーバの追加オーバーヘッドという犠牲が付随することに留意してください。サーバでの余分なチェックによってパフォーマンスに影響が生じるため、一部のアプリケーションでは、可能であればこれを避けるよう設定されています。(このため、主要な商用アプリケーションの中には、アプリケーションレベルでこの外部キーロジックがコード化されているものもあります。)

MySQLでは、データベース開発者が使用するアプローチを選択できます。外部キーが不必要で、かつ参照整合性をチェックすることで生じるオーバーヘッドを避ける必要がなければ、MyISAMのような他のストレージエンジンを代わりに選択できます。(たとえば、MyISAMストレージエンジンでは、INSERTやSELECTオペレーションでのみ行なわれるアプリケーションのパフォーマンスが、非常に高速に行なわれるようになります。この場合、テーブルの中間に欠如はなく、挿入は検索と同時に行なわれます。項6.3.3. 「同時挿入」参照。)

参照整合性チェックを利用しない場合は、次のことに留意してください。

サーバ側の外部キー関係チェックを使用しない場合、アプリケーション自身で関係についての問題を処理しなければなりません。たとえば、適切な順序でテーブルに行を挿入し、分断段落レコードの作成を避けるように注意してください。複合レコード挿入オペレーションの中間で生じるエラーから回復することも可能です。
ON DELETEが、アプリケーションが必要とする参照整合性能力のみの場合、１つのステートメントで多くのテーブルから行を削除するために、複数テーブルのDELETEステートメントを使用してMySQL4.0サーバと同様の結果を得ることができます。項12.2.1. 「DELETE 構文」を参照してください。
ON DELETEが実装されていないという問題に対処するには、外部キーがあるテーブルからレコードを削除する際に適切なDELETEステートメントをアプリケーションに追加します。実際、この方法は外部キーを使用する場合と同じくらい簡単で、移植性はそれよりもはるかに高くなります。

外部キーを使用するデメリットは以下のとおりです。

外部キーサポートは多くの参照性合成問題をアドレス指定しますが、キー関係を設計する上で犯しやすい間違いによって、循環ルール、連鎖削除の不適切な組み合わせなどの深刻な問題が生じることがあります。
DBA にとって、個々のテーブルのバックアップやリストアが非常に困難になり、場合によっては不可能になるような複雑な関係のトポロジを作成することはめったにありません。(MySQLは、他のテーブルに依存するテーブルをリロードする際、一時的に外部キーチェックを使用できないようにすることで、この問題を軽減します。詳しくは項13.5.6.4. 「FOREIGN KEY 制約」を参照してください。MySQL 4.1.1以降では、mysqldumpは、リロードの際に自動的にこの能力を利用するダンプファイルを生成します。）

SQLの外部キーは、テーブルを結合させずに参照整合性をチェックおよび実行します。SELECTステートメントで複数テーブルからの結果を得たい場合は、以下のようにjoinを行なってください。

SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;

項12.2.7.1. 「JOIN 構文」およびUsing Foreign Keysを参照してください。

ON DELETE ...を使用しないFOREIGN KEY構文は、自動的にWHERE節を作成するために、ODBCアプリケーションで使用されることが多々あります。