mysqlの外部キー制約
InnoDBでは外部キーコンストレイントもサポートされています.InnoDBの外部キー制約定義の構文は次のように見えます.
外部キー定義は次の場合に従います.
・すべてのtablesはInnoDB型でなければなりません.一時テーブルではありません.
・参照テーブルには、外部キー列が同じ順序で最初の列として列挙されるインデックスが必要です.このようなインデックスが存在しない場合は、参照テーブルに自動的に作成される必要があります.
・参照テーブルには、最初のカラムとして同じ順序で列挙されたインデックスが必要です.
・外部キー列のインデックス接頭辞はサポートされていません.このような結果の1つは、BLOB列とTEXT列が外部キーに含まれないことであり、これらの列のインデックスには常にプレフィックス長が含まれなければならないからである.
・COSTRAINTsymbolが与えられる場合、データベース内で一意である必要があります.与えられていない場合、InnoDBは自動的にこの名前を作成します.
InnoDBは、親テーブルの候補キー値に一致しないサブテーブルに外部キー値を作成しようとするINSERTまたはUPDATE操作を拒否します.親テーブルには一致する行のサブテーブルがあり、InnoDBは親テーブルの候補キー値を更新または削除しようとするUPDATEまたはDELETE操作に対して動作します.この動作は、FOREGN KEY句のON UPDATEおよびON DETETE句で指定されたreferenceal actionに依存します.親テーブルからローを削除または更新しようとすると、サブテーブルに一致するローが1つまたは複数存在する場合、InnoDBは次の5つの選択肢を選択します.
・CASCADE:親テーブルから一致するローを削除または更新し、サブテーブルから一致するローを自動的に削除または更新します.ON DELETE CASCADEとON UPDATE CASCADEの両方が使用可能です.2つのテーブルの間で、親テーブルまたは子テーブルの同じ列で動作するON UPDATE CASCADE句をいくつか定義する必要はありません.
・SET NULL:親テーブルからローを削除または更新し、サブテーブルの外部キー列をNULLに設定します.外部キー列にNOT NULL限定語が指定されていない場合、これは唯一の合法的です.ON DELETE SET NULLおよびON UPDATE SET NULL句がサポートされています.
・NO ACTION:ANSI SQL-92規格において、NO ACTIONは、これが動作しないことを意味し、関連する外部キー値が参照されるテーブルにある場合、プライマリキー値を削除または更新しようとする企みが許可されないことを意味する(Gruber、SQLを把握、2000:181).InnoDBは親テーブルの削除または更新を拒否します.
・RESTRICT:親テーブルの削除または更新を拒否します.NO ACTIONもRESTRICTも同様に、ON DELETEまたはON UPDATE句を削除します.(一部のデータベースシステムでは、遅延チェックがあり、NO ACTIONは遅延チェックです.MySQLでは、外部キー制約はすぐにチェックされるので、NO ACTIONとRESTRICTは同じです).
・SET DEFAULT:この動作は解析プログラムによって認識されるが、InnoDBは、ON DELETE SET DEFAULTまたはON UPDATE SET DEFAULT句を含むテーブル定義を拒否する.
親テーブルの候補キーが更新されると、InnoDBは同様の選択をサポートします.CASCADEを選択すると、サブテーブルの外部キー列が親テーブルの候補キーの新しい値に設定されます.同様に、サブテーブルで更新されたカラムが別のテーブルの外部キーを参照する場合、カスケードが更新されます.
なお、InnoDBは外部キーの1つのテーブル内参照をサポートしており、これらの場合、サブテーブルは実際にはテーブル内に付属するレコードを意味する.
InnoDBは、外部キーと参照キーのインデックスを必要とし、外部キーチェックが迅速に行われ、テーブルスキャンが不要になるようにします.外部キーのインデックスが自動的に作成されます.これは、古いバージョンに比べて、古いバージョンではインデックスが明確に作成されなければなりません.そうしないと、外部キー制約の作成に失敗します.
InnoDBでは、外部キーと参照されるカラムの対応するカラムには、タイプ変換を必要とせずに比較できるように、類似の内部データ型が必要です.整数タイプのサイズと記号は同じでなければなりません.文字列タイプの長さは同じではありません.SET NULLアクションを指定した場合は、サブテーブルにNOT NULLとして宣言されていないことを確認します.
MySQLがCREATE TABLE文からエラー番号1005を報告し、エラー情報文字列がerrno 150を指す場合、外部キー制約が正しく形成されず、テーブルの作成に失敗したことを意味します.同様に、ALTER TABLEが失敗し、errno 150を指す場合、変更されたテーブルに対して外部キー定義が正しく形成されないことを意味する.SHOW INNODB STATUSを使用して、サーバ上の最近のInnoDB外部キーエラーの詳細な説明を表示できます.
注記:InnoDBは、外部キーまたはNULL列を含む参照キー値に対して外部キー制約をチェックしません.
SQL標準の乖離:親テーブル内に複数の行があり、同じ参照キー値がある場合、InnoDBは外部キーチェックで動作します.同じキー値を持つ他の親行が存在しないようにします.たとえば、RESTRICTタイプの制約を定義し、複数の親行を持つ子行がある場合、InnoDBではこれらの親行の削除は許可されません.
外部キー制約に対応するインデックス内のレコードに位置し,InnoDBは深さ優先選択法によりカスケード操作を行う.
SQL標準の乖離:ON UPDATE CASCADEまたはON UPDATE SET NULLが同じテーブルを再帰的に更新すると、カスケード中にテーブルが更新されると、RESTRICTのように動作します.これは、自己参照ON UPDATE CASCADEまたはON UPDATE SET NULL操作を使用できないことを意味します.これにより、カスケード更新による無限ループがブロックされます.一方、自己参照のON DELETE SET NULLは、自己参照のON DELETE CASCADEのように可能である.カスケード操作は15層以上の深さでネストすることはできません.
SQL標準からの乖離:一般的なMySQLと同様に、多くの行を挿入、削除または更新するSQL文内で、InnoDBはUNIQUEおよびFOREIGN KEY制約を行単位でチェックします.SQLの基準に従って、デフォルトの動作は遅延チェックされるべきです.すなわち、制約はSQL文全体が処理された後にのみチェックされます.InnoDBが遅延制約チェックを実現するまで、外部キーを介して自身に参照されたレコードを削除するなど、いくつかのことは不可能です.
注記:現在、トリガはカスケード外部キーの動作によってアクティブになりません.
親テーブルと子テーブルを1列の外部キーで関連付ける簡単な例は、次のとおりです.
以下に、product_の1つをより複雑な例として示します.orderテーブルには、他の2つのテーブルに外部キーがあります.外部キーはproductテーブルの2列インデックスを参照します.customerテーブル内のもう1つのインデックスを参照するには、次の手順に従います.
InnoDBでは、ALTER TABLEを使用してテーブルに新しい外部キー制約を追加できます.
必要なインデックスを先に作成することを覚えておいてください.また、ALTER TABLEを使用して、テーブルに自己参照外部キー制約を追加することもできます.
InnoDBでは、ALTER TABLEを使用して外部キーを除去することもサポートされています.
外部キーを作成したとき、FOREGN KEY句にCONTRAINT名が含まれている場合は、その名前を参照して外部キーを削除できます.また、外部キーが作成されるとfk_Symbol値はInnoDB内部で保証されている.外部キーを削除したい場合は、マークを見つけるにはSHOW CREATE TABLE文を使用します.例は次のとおりです.
InnoDB解析プログラムでFOREGN KEYで...REFERENCES ...句の中で`(backticks)で表と列名の名前を囲みます.InnoDB解析プログラムもlower_を考慮case_table_namesシステム変数の設定.
InnoDBは、SHOW CREATE TABLE文の出力の一部としてテーブルの外部キー定義を返します.
このバージョンからmysqldumpもテーブルの正確な定義をダンプファイルに生成し、外部キーを忘れません.
外部キー制約を次のように表に表示できます.
外部キー制約は、出力されたComment列に列挙されます.
外部キーチェックが実行されると、InnoDBは、見ている子または親レコードに対して共有行レベルロックを設定します.InnoDBはすぐに外部キー制約をチェックし、トランザクションのコミットが遅延しないことをチェックします.
外部キー関係のあるテーブルのダンプファイルの再ロードを容易にするには、mysqldumpがダンプ出力に自動的に文を含めてFOREIGN_を設定します.KEY_CHECKSは0です.これにより、ダンプが再ロードされる際に、特別な順序で再ロードせざるを得ないテーブルに関連する問題が回避される.この変数を手動で設定することもできます.
ダンプ・ファイルに外部キーが正しくない順序のテーブルが含まれている場合は、テーブルを任意の順序でインポートします.これにより、インポート操作も高速化されます.FOREGN_の設定KEY_CHECKSは0であり,LOAD DATAおよびALTER TABLE動作において外部キー制限を無視するのにも有用である.
InnoDBでは、SET FOREGN_を設定しない限り、FOREGN KEYテーブル制約で参照されているテーブルを削除することはできません.KEY_CHECKS=0.テーブルを削除すると、その作成文で定義された制約も削除されます.
削除されたテーブルを再作成する場合は、外部キーコンストレイントも参照する定義に従う必要があります.正しいカラム名とタイプが必要であり、前述したように、参照されるキーにインデックスが必要です.これらが満たされない場合、MySQLはエラー番号1005を返し、エラー情報文字列でerrno 150を指す.
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
外部キー定義は次の場合に従います.
・すべてのtablesはInnoDB型でなければなりません.一時テーブルではありません.
・参照テーブルには、外部キー列が同じ順序で最初の列として列挙されるインデックスが必要です.このようなインデックスが存在しない場合は、参照テーブルに自動的に作成される必要があります.
・参照テーブルには、最初のカラムとして同じ順序で列挙されたインデックスが必要です.
・外部キー列のインデックス接頭辞はサポートされていません.このような結果の1つは、BLOB列とTEXT列が外部キーに含まれないことであり、これらの列のインデックスには常にプレフィックス長が含まれなければならないからである.
・COSTRAINTsymbolが与えられる場合、データベース内で一意である必要があります.与えられていない場合、InnoDBは自動的にこの名前を作成します.
InnoDBは、親テーブルの候補キー値に一致しないサブテーブルに外部キー値を作成しようとするINSERTまたはUPDATE操作を拒否します.親テーブルには一致する行のサブテーブルがあり、InnoDBは親テーブルの候補キー値を更新または削除しようとするUPDATEまたはDELETE操作に対して動作します.この動作は、FOREGN KEY句のON UPDATEおよびON DETETE句で指定されたreferenceal actionに依存します.親テーブルからローを削除または更新しようとすると、サブテーブルに一致するローが1つまたは複数存在する場合、InnoDBは次の5つの選択肢を選択します.
・CASCADE:親テーブルから一致するローを削除または更新し、サブテーブルから一致するローを自動的に削除または更新します.ON DELETE CASCADEとON UPDATE CASCADEの両方が使用可能です.2つのテーブルの間で、親テーブルまたは子テーブルの同じ列で動作するON UPDATE CASCADE句をいくつか定義する必要はありません.
・SET NULL:親テーブルからローを削除または更新し、サブテーブルの外部キー列をNULLに設定します.外部キー列にNOT NULL限定語が指定されていない場合、これは唯一の合法的です.ON DELETE SET NULLおよびON UPDATE SET NULL句がサポートされています.
・NO ACTION:ANSI SQL-92規格において、NO ACTIONは、これが動作しないことを意味し、関連する外部キー値が参照されるテーブルにある場合、プライマリキー値を削除または更新しようとする企みが許可されないことを意味する(Gruber、SQLを把握、2000:181).InnoDBは親テーブルの削除または更新を拒否します.
・RESTRICT:親テーブルの削除または更新を拒否します.NO ACTIONもRESTRICTも同様に、ON DELETEまたはON UPDATE句を削除します.(一部のデータベースシステムでは、遅延チェックがあり、NO ACTIONは遅延チェックです.MySQLでは、外部キー制約はすぐにチェックされるので、NO ACTIONとRESTRICTは同じです).
・SET DEFAULT:この動作は解析プログラムによって認識されるが、InnoDBは、ON DELETE SET DEFAULTまたはON UPDATE SET DEFAULT句を含むテーブル定義を拒否する.
親テーブルの候補キーが更新されると、InnoDBは同様の選択をサポートします.CASCADEを選択すると、サブテーブルの外部キー列が親テーブルの候補キーの新しい値に設定されます.同様に、サブテーブルで更新されたカラムが別のテーブルの外部キーを参照する場合、カスケードが更新されます.
なお、InnoDBは外部キーの1つのテーブル内参照をサポートしており、これらの場合、サブテーブルは実際にはテーブル内に付属するレコードを意味する.
InnoDBは、外部キーと参照キーのインデックスを必要とし、外部キーチェックが迅速に行われ、テーブルスキャンが不要になるようにします.外部キーのインデックスが自動的に作成されます.これは、古いバージョンに比べて、古いバージョンではインデックスが明確に作成されなければなりません.そうしないと、外部キー制約の作成に失敗します.
InnoDBでは、外部キーと参照されるカラムの対応するカラムには、タイプ変換を必要とせずに比較できるように、類似の内部データ型が必要です.整数タイプのサイズと記号は同じでなければなりません.文字列タイプの長さは同じではありません.SET NULLアクションを指定した場合は、サブテーブルにNOT NULLとして宣言されていないことを確認します.
MySQLがCREATE TABLE文からエラー番号1005を報告し、エラー情報文字列がerrno 150を指す場合、外部キー制約が正しく形成されず、テーブルの作成に失敗したことを意味します.同様に、ALTER TABLEが失敗し、errno 150を指す場合、変更されたテーブルに対して外部キー定義が正しく形成されないことを意味する.SHOW INNODB STATUSを使用して、サーバ上の最近のInnoDB外部キーエラーの詳細な説明を表示できます.
注記:InnoDBは、外部キーまたはNULL列を含む参照キー値に対して外部キー制約をチェックしません.
SQL標準の乖離:親テーブル内に複数の行があり、同じ参照キー値がある場合、InnoDBは外部キーチェックで動作します.同じキー値を持つ他の親行が存在しないようにします.たとえば、RESTRICTタイプの制約を定義し、複数の親行を持つ子行がある場合、InnoDBではこれらの親行の削除は許可されません.
外部キー制約に対応するインデックス内のレコードに位置し,InnoDBは深さ優先選択法によりカスケード操作を行う.
SQL標準の乖離:ON UPDATE CASCADEまたはON UPDATE SET NULLが同じテーブルを再帰的に更新すると、カスケード中にテーブルが更新されると、RESTRICTのように動作します.これは、自己参照ON UPDATE CASCADEまたはON UPDATE SET NULL操作を使用できないことを意味します.これにより、カスケード更新による無限ループがブロックされます.一方、自己参照のON DELETE SET NULLは、自己参照のON DELETE CASCADEのように可能である.カスケード操作は15層以上の深さでネストすることはできません.
SQL標準からの乖離:一般的なMySQLと同様に、多くの行を挿入、削除または更新するSQL文内で、InnoDBはUNIQUEおよびFOREIGN KEY制約を行単位でチェックします.SQLの基準に従って、デフォルトの動作は遅延チェックされるべきです.すなわち、制約はSQL文全体が処理された後にのみチェックされます.InnoDBが遅延制約チェックを実現するまで、外部キーを介して自身に参照されたレコードを削除するなど、いくつかのことは不可能です.
注記:現在、トリガはカスケード外部キーの動作によってアクティブになりません.
親テーブルと子テーブルを1列の外部キーで関連付ける簡単な例は、次のとおりです.
CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY (id)
) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) TYPE=INNODB;
以下に、product_の1つをより複雑な例として示します.orderテーブルには、他の2つのテーブルに外部キーがあります.外部キーはproductテーブルの2列インデックスを参照します.customerテーブル内のもう1つのインデックスを参照するには、次の手順に従います.
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX (customer_id),
FOREIGN KEY (customer_id)
REFERENCES customer(id)) TYPE=INNODB;
InnoDBでは、ALTER TABLEを使用してテーブルに新しい外部キー制約を追加できます.
ALTER TABLE yourtablename
ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
必要なインデックスを先に作成することを覚えておいてください.また、ALTER TABLEを使用して、テーブルに自己参照外部キー制約を追加することもできます.
InnoDBでは、ALTER TABLEを使用して外部キーを除去することもサポートされています.
ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;
外部キーを作成したとき、FOREGN KEY句にCONTRAINT名が含まれている場合は、その名前を参照して外部キーを削除できます.また、外部キーが作成されるとfk_Symbol値はInnoDB内部で保証されている.外部キーを削除したい場合は、マークを見つけるにはSHOW CREATE TABLE文を使用します.例は次のとおりです.
mysql> SHOW CREATE TABLE ibtest 11c /G
*************************** 1. row ***************************
Table: ibtest
11c
Create Table: CREATE TABLE `ibtest
11c
` (
`A` int(11) NOT NULL auto_increment,
`D` int(11) NOT NULL default '0',
`B` varchar(200) NOT NULL default '',
`C` varchar(175) default NULL,
PRIMARY KEY (`A`,`D`,`B`),
KEY `B` (`B`,`C`),
KEY `C` (`C`),
CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest
11a
` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest
11a
` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARSET=latin1
1 row in set (0.01 sec)
mysql> ALTER TABLE ibtest 11c DROP FOREIGN KEY 0_38775;
InnoDB解析プログラムでFOREGN KEYで...REFERENCES ...句の中で`(backticks)で表と列名の名前を囲みます.InnoDB解析プログラムもlower_を考慮case_table_namesシステム変数の設定.
InnoDBは、SHOW CREATE TABLE文の出力の一部としてテーブルの外部キー定義を返します.
SHOW CREATE TABLE tbl_name;
このバージョンからmysqldumpもテーブルの正確な定義をダンプファイルに生成し、外部キーを忘れません.
外部キー制約を次のように表に表示できます.
SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';
外部キー制約は、出力されたComment列に列挙されます.
外部キーチェックが実行されると、InnoDBは、見ている子または親レコードに対して共有行レベルロックを設定します.InnoDBはすぐに外部キー制約をチェックし、トランザクションのコミットが遅延しないことをチェックします.
外部キー関係のあるテーブルのダンプファイルの再ロードを容易にするには、mysqldumpがダンプ出力に自動的に文を含めてFOREIGN_を設定します.KEY_CHECKSは0です.これにより、ダンプが再ロードされる際に、特別な順序で再ロードせざるを得ないテーブルに関連する問題が回避される.この変数を手動で設定することもできます.
mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;
ダンプ・ファイルに外部キーが正しくない順序のテーブルが含まれている場合は、テーブルを任意の順序でインポートします.これにより、インポート操作も高速化されます.FOREGN_の設定KEY_CHECKSは0であり,LOAD DATAおよびALTER TABLE動作において外部キー制限を無視するのにも有用である.
InnoDBでは、SET FOREGN_を設定しない限り、FOREGN KEYテーブル制約で参照されているテーブルを削除することはできません.KEY_CHECKS=0.テーブルを削除すると、その作成文で定義された制約も削除されます.
削除されたテーブルを再作成する場合は、外部キーコンストレイントも参照する定義に従う必要があります.正しいカラム名とタイプが必要であり、前述したように、参照されるキーにインデックスが必要です.これらが満たされない場合、MySQLはエラー番号1005を返し、エラー情報文字列でerrno 150を指す.