MySQL 外部キー制約(FOREIGN KEY)

MySQLにおける外部キー制約について説明します。

あるカラムに外部キー制約を設定しておくと、そのカラムは他テーブルのカラムの値だけに
限定することができます。
ですが、いくつか条件があります。

・親テーブルと子テーブルが両方InnoDBであること
・子カラムと親カラムの型が一致していること
・子カラムに親カラム以外の値が入っていないこと
・親カラムにインデックスが設定されていること

細かいのは他にもありますが、大きくはこの三つです。
外部キー制約を設定するカラムが子カラムで、参照される側が親カラムです。

mysql_query関数やコマンドラインで以下のように設定します。

CREATE TABLE test(no int,sample int,
  FOREIGN KEY (no) REFERENCES seihin(id));

上記のコードの場合は、testテーブルのnoというカラムに外部キー制約を設定しています。
noというカラムは「seihin」テーブルの「id」カラムの値だけに限定されます。

外部キーは後から設定することも出来ます。
その場合はALTER TABLEを使います。

ALTER TABLE test ADD FOREIGN KEY(no) REFERENCES seihin(id);

親テーブルにない値を入れようとするとエラーになります。
外部キー設定は非常によく使われるので覚えておきましょう。

親テーブルの値更新 or 削除時の設定(ON UPDATE,ON DELETE)

外部キー設定の説明はしましたが、もし親テーブルのレコードの値を更新したり、
削除したりした場合は子テーブルはどうなるでしょうか？

デフォルトの動作ではエラーになり、更新したり削除することができません。
ですが、そのときの動作を外部キー設定時に設定することができます。
以下のようなコマンドを書きます。

ALTER TABLE test ADD FOREIGN KEY(no) REFERENCES seihin(id) ON UPDATE オプション名 ON DELETE オプション名;

後ろにON UPDATEとON DELETEをくっつけてオプション名を書きます。
オプションは以下のものがあります。

RESTRICT エラーが発生（デフォルト）
NO ACTION　エラーが発生
CASCADE 親テーブルに行われた処理と同じ処理が子テーブルにも行われる
SET NULL　子テーブルにはNULLが設定される。

何も設定しなかった場合はデフォルトのRESTRICTでエラーが発生します。
NO ACTIONもエラーが発生します。
CASCADEは書いてる通り、親テーブルの値が5→10に変更された場合、
子テーブルの値も5→10に更新されます。
親テーブルのレコード削除時も子テーブルの方もレコードごと削除されます。
SET NULLは書いてあるとおりです。

この通り外部キーを設定しておけば、データベースそれぞれに関連性を持たせることが出来ます。
>> 【MySQL ストレージエンジンの設定、変更】に進む
>> MySQL入門トップに戻る

おすすめ参考書