MySQL外部キー制約
3073 ワード
MySQLには、MyISAMとInnoDBの2つの一般的なエンジンタイプがあります.外部キー制約はInnoDBエンジンタイプのみでサポートされています.InnoDBの外部キー制約定義の構文は次のとおりです.
外部キーを使用するには、次の条件を満たす必要があります.
1. 両方のテーブルはInnoDBテーブルでなければなりません.テンポラリ・テーブルはありません.
2. 外部キー関係を確立する対応するカラムには、似たようなInnoDB内部データ型が必要です.
3. 外部キー関係を確立する対応するカラムには、インデックスが作成されている必要があります.
4. CONTRAINT symbolが明示的に与えられている場合、symbolはデータベース内で一意である必要があります.明示的に与えられていない場合、InnoDBは自動的に作成されます.
子テーブルが親テーブルに存在しない外部キー値を作成しようとすると、InnoDBはINSERTまたはUPDATE操作を拒否します.親テーブルがUPDATEまたはDELETEのいずれかのサブテーブルに存在または一致する外部キー値をしようとする場合、最終的な動作は、外部キー制約定義のON UPDATEおよびON DELETEオプションに依存します.InnoDBは5種類の異なる動作をサポートしているが、ON DELETEまたはON UPDATEが指定されていない場合、デフォルトの動作はRESTRICTである.
1. CASCADE:親テーブルから対応するローを削除または更新し、テーブルから一致するローを自動的に削除または更新します.ON DELETE CANSCADEとON UPDATE CANSCADEの両方がInnoDBでサポートされています.
2. SET NULL:親テーブルから対応するローを削除または更新し、サブテーブルの外部キー列を空にします.なお、これらは、外部キー列がNOT NULLに設定されていない場合にのみ有効である.ON DELETE SET NULLおよびON UPDATE SET NULLは、いずれもInnoDBによってサポートされています.
3. NO ACTION:InnoDBは親テーブルの削除または更新を拒否します.
4. RESTRICT:親テーブルの削除または更新を拒否します.RESTRICT(またはNO ACTION)を指定するのと、ON DELETEまたはON UPDATEオプションを無視するのと同じ効果です.
5. SET DEFAULT:InnoDBは現在サポートされていません.
外部キーコンストレイントで最も多く使用されるのは、次の2つです.
1)親テーブルの更新時に子テーブルも更新され、親テーブルの削除時に子テーブルに一致する項目がある場合、削除に失敗します.
2)親テーブルが更新されると子テーブルも更新され、親テーブルが削除されると子テーブルが一致するアイテムも削除されます.
前の場合、外部キー定義では、ON UPDATE CASCADE ON DELETE RESTRICTを使用します.後者の場合、ON UPDATE CASCADE ON DELETE CASCADEを使用できます.
InnoDBでは、ALTER TABLEを使用して既存のテーブルに新しい外部キーを追加できます.
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION外部キーを使用するには、次の条件を満たす必要があります.
1. 両方のテーブルはInnoDBテーブルでなければなりません.テンポラリ・テーブルはありません.
2. 外部キー関係を確立する対応するカラムには、似たようなInnoDB内部データ型が必要です.
3. 外部キー関係を確立する対応するカラムには、インデックスが作成されている必要があります.
4. CONTRAINT symbolが明示的に与えられている場合、symbolはデータベース内で一意である必要があります.明示的に与えられていない場合、InnoDBは自動的に作成されます.
子テーブルが親テーブルに存在しない外部キー値を作成しようとすると、InnoDBはINSERTまたはUPDATE操作を拒否します.親テーブルがUPDATEまたはDELETEのいずれかのサブテーブルに存在または一致する外部キー値をしようとする場合、最終的な動作は、外部キー制約定義のON UPDATEおよびON DELETEオプションに依存します.InnoDBは5種類の異なる動作をサポートしているが、ON DELETEまたはON UPDATEが指定されていない場合、デフォルトの動作はRESTRICTである.
1. CASCADE:親テーブルから対応するローを削除または更新し、テーブルから一致するローを自動的に削除または更新します.ON DELETE CANSCADEとON UPDATE CANSCADEの両方がInnoDBでサポートされています.
2. SET NULL:親テーブルから対応するローを削除または更新し、サブテーブルの外部キー列を空にします.なお、これらは、外部キー列がNOT NULLに設定されていない場合にのみ有効である.ON DELETE SET NULLおよびON UPDATE SET NULLは、いずれもInnoDBによってサポートされています.
3. NO ACTION:InnoDBは親テーブルの削除または更新を拒否します.
4. RESTRICT:親テーブルの削除または更新を拒否します.RESTRICT(またはNO ACTION)を指定するのと、ON DELETEまたはON UPDATEオプションを無視するのと同じ効果です.
5. SET DEFAULT:InnoDBは現在サポートされていません.
外部キーコンストレイントで最も多く使用されるのは、次の2つです.
1)親テーブルの更新時に子テーブルも更新され、親テーブルの削除時に子テーブルに一致する項目がある場合、削除に失敗します.
2)親テーブルが更新されると子テーブルも更新され、親テーブルが削除されると子テーブルが一致するアイテムも削除されます.
前の場合、外部キー定義では、ON UPDATE CASCADE ON DELETE RESTRICTを使用します.後者の場合、ON UPDATE CASCADE ON DELETE CASCADEを使用できます.
InnoDBでは、ALTER TABLEを使用して既存のテーブルに新しい外部キーを追加できます.
ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
InnoDBでは、ALTER TABLEを して キーを することもサポートされています.ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
：MySQL 5.1 Manual: FOREIGN KEY Constraints