【外部キー制約とは】
子テーブル側の外部キーと、親テーブルの主キーの参照整合性を維持するために、子テーブル側の外部キーの動作を制限するものです。
たとえば、前回のブランドと取扱商品との関係性では、取扱商品テーブル側に不明な外部キー（[ブランドID]）が存在しないように制御したり、ブランドテーブル側から特定のブランドを削除した場合、そのブランドに関連する商品を取扱商品から自動的に連動して削除させたりといった振る舞い（制約）を設定します。
MySQL Workbench の EER モデリングでは、下図のように Foreign Key タブ上の Foreign Key Option を選択するだけで外部キー制約の設定が行えます。
【On Update と On Delete】
外部キー制約のオプションは、更新時（On Update）、削除時（On Delete）に大別されます。
これらは、親テーブル側（上図ではブランド）側の動作を示しており、親テーブル側のレコードが更新されたり（On Update）、削除されたり（On Delete）したときに、子テーブル（上図では取扱商品）の関連レコードの扱いを定義するという意味です。
そして、実際の関連レコードの扱いは、上図の赤囲みのドロップダウンリストから選択します。
【外部キー制約の種類】
ドロップダウンリストから選択可能な外部キー制約は次のとおりです。
RESTRICT/NO ACTION
子テーブルに関連レコードがあったら、親レコードの更新/削除を許可しない。
NO ACTION はステートメントおよびすべてのトリガーが起動された後に子テーブルの値がまだ有効であれば変更は許可される。
これに対し、RESTRICT は、子テーブルに関連レコードが存在する場合、変更は絶対に許可されない。
RDBMS ではこのような厳密な動作の違いがあるものもあるが、MySQL では RESTRICT と NO ACTION の挙動は同じ。
たとえば、取扱商品（子テーブル側）の登録を始めたら、ブランド名（親テーブル側）の変更をさせないようにする場合がこれに相当します。
CASCADE
親テーブルのレコードを更新/削除したら、子テーブルのレコードも更新/削除する。
厳密には、親テーブルの主キーの値を更新したら、子テーブルの外部キーの値も同じ値で更新され、親テーブルのレコードを削除したら、そのレコードに関連する子テーブルのレコードも連動して削除される。
たとえば、特定のブランド（親テーブル側）を削除したら、そのブランドを持つすべての取扱商品（子テーブル側）を削除するようにする場合がこれに相当します。
SET NULL
親テーブルのレコードを更新/削除したら、子テーブルの外部キーの値を NULL に設定する。
これにより、当該レコードの親子関係が解除されることになる。
たとえば、ブランド（親テーブル側）を削除したとしても、取扱商品（子テーブル側）は将来の管理用にデッドストックとして残しておく場合がこれに相当します。
親テーブルの主キーを変更した場合、および親レコードを削除した場合の外部キー制約の振る舞いを表にすると、以下のようになります。
| 動作条件 | 挙動 | 親（子なし） | 親（子あり） | 子 |
|---|---|---|---|---|
| On Update | RESTRICT/NO ACTION | 親主キー更新可 | 親主キー更新不可 | 変化なし |
| CASCADE | 親主キー更新可 | 親主キー更新可 | 外部キー連動更新 | |
| SET NULL | 親主キー更新可 | 親主キー更新可 | 外部キーNULL | |
| On Delete | RESTRICT/NO ACTION | 親レコード削除可 | 親レコード削除不可 | 変化なし |
| CASCADE | 親レコード削除可 | 親レコード削除可 | 子レコード連動削除 | |
| SET NULL | 親レコード削除可 | 親レコード削除可 | 外部キーNULL |
【SQL による外部キー制約の例】
前述の外部キー制約のパターンのうちのいくつかを、実際の SQL による定義でいくつか確認してみましょう。太字が外部キーの設定と制約を定義している部分です。特に赤字の部分に注目してください。
挙動については、前項のテーブルをご覧ください。
例1:
On Update: NO ACTION
On Delete: CASCADE
--
-- Table structure for table `取扱商品`
--
DROP TABLE IF EXISTS `取扱商品`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `取扱商品` (
`ブランドID` int(11) DEFAULT NULL,
`商品ID` int(11) NOT NULL AUTO_INCREMENT,
`商品名` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`商品ID`),
KEY `fk_取扱商品_ブランド` (`ブランドID`),
CONSTRAINT `fk_取扱商品_ブランド` FOREIGN KEY (`ブランドID`) REFERENCES `ブランド` (`ブランドID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
例2:
On Update: CASCADE
On Delete: SET NULL
--
-- Table structure for table `取扱商品`
--
DROP TABLE IF EXISTS `取扱商品`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `取扱商品` (
`ブランドID` int(11) DEFAULT NULL,
`商品ID` int(11) NOT NULL AUTO_INCREMENT,
`商品名` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`商品ID`),
KEY `fk_取扱商品_ブランド` (`ブランドID`),
CONSTRAINT `fk_取扱商品_ブランド` FOREIGN KEY (`ブランドID`) REFERENCES `ブランド` (`ブランドID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
0 件のコメント:
コメントを投稿