[MySQL] 外部キー制約を設定してリレーションを頑強にする

MySQL覚え書き。必須じゃないけど考慮しておくといざというときに役に立ちそうな「外部キー制約」。

外部キー制約とは

あるテーブルから別のテーブルを参照するために、別のテーブルのIDを「外部キー」として格納している場合、別のテーブルのIDが勝手に変更されたり削除されたりすると、２つのテーブル間の紐付けができなくなってしまう。これを避けるために、テーブル間の連携で使われる値に一定の条件を設けておくと良い。これを行うのが「外部キー制約」である。

外部キー制約を設定することで

• 参照する側に挿入されるデータを、参照される側に存在しているデータのみに限定することができる。
• 参照される側の値を変更した場合には、エラーを表示するか、参照する側のデータを同様の値で更新またはNULL値を挿入することができる。
• 参照される側の値が削除される場合も、エラーを表示するか、参照する側のデータを削除またはNULL値を挿入することができる。

外部キー制約の設定方法

定義済みのテーブルに対して外部キー制約を設定するには以下のようにする。

ALTER TABLE テーブル名
ADD FOREIGN KEY 外部キー名
REFERENCES 元テーブル名 (元キー名)
ON UPDATE 更新時制約
ON DELETE 削除時制約;

更新時と削除時の制約条件はマニュアルを参照のこと。手っ取り早く更新と削除を禁止してエラーとするならRESTRICTと指定する。

ALTER TABLE products
ADD FOREIGN KEY category_id
REFERENCES categories (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT;

外部キー制約を設定しておくことで、例外的なデータベース操作が行われたときのフェイルセーフを向上させることができそうに思える。システムを強固にするために、考慮しておくに越したことはない。

参考サイト

MySQL :: MySQL 5.1 リファレンスマニュアル :: 13.5.6.4 FOREIGN KEY 制約
http://dev.mysql.com/doc/refman/5.1/ja/innodb-foreign-key-constraints.html

今から始める MySQL入門（8）：外部キー制約を活用する (1/3) – ＠IT
http://www.atmarkit.co.jp/ait/articles/0707/17/news113.html

MySQL 外部キー制約(FOREIGN KEY) | MySQL入門～bituse～
http://bituse.info/mysql/17