MySQL :: MySQL 5.6 リファレンスマニュアル

8.0 English
5.7 English
5.6 English
5.5 English

MySQL 5.6 リファレンスマニュアル / ... / 外部キーの違い

1.7.2.4 外部キーの違い

InnoDB ストレージエンジンは、CASCADE、ON DELETE、ON UPDATE などの外部キー制約のチェックをサポートします。セクション14.6.6「InnoDB と FOREIGN KEY 制約」を参照してください。

InnoDB および NDB 以外のストレージエンジンの場合、MySQL Server は CREATE TABLE ステートメントの FOREIGN KEY 構文を解析しますが、これを使用したり格納したりしません。この情報は、mysqldump にも存在し、Connector/ODBC を使用して取得できます。INFORMATION_SCHEMA 情報データベース内の INFORMATION_SCHEMA.TABLE_CONSTRAINTS テーブルをチェックすることによって、どのテーブルに外部キー制約があるかを確認できます。INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS テーブルから、外部キーに関する詳細を取得できます。さらに、InnoDB には、InnoDB テーブル上の外部キーに関する情報を含む多数の INFORMATION_SCHEMA テーブルが用意されています。セクション21.29「InnoDB の INFORMATION_SCHEMA テーブル」を参照してください。

データベース開発者にとって、次のような外部キーを使用するメリットがあります。

関係が適切に設計されている場合、外部キー制約によって、プログラマがデータベースで不整合を引き起こすことが少なくなります。
データベースサーバーによって集中的に制約チェックが行われるため、アプリケーション側でのこれらのチェックが不要になります。このことで、さまざまなアプリケーションで制約が同じようにチェックされない場合があるという可能性がなくなります。
連鎖更新および削除を使用すると、アプリケーションコードを単純化することができます。
適切に設計された外部キールールは、テーブル間の関係の記述に役立ちます。

SQL の外部キーは、テーブルの結合ではなく、参照整合性のチェックと実施に使用されます。SELECT ステートメントで複数のテーブルからの結果を取得する場合は、次のようにそれらの結合を実行してください。

SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;

セクション13.2.9.2「JOIN 構文」およびセクション3.6.6「外部キーの使用」を参照してください。

ON DELETE ... を使用しない FOREIGN KEY 構文は、自動的に WHERE 句を作成するために、ODBC アプリケーションで使用されることがよくあります。

SQL 標準からの逸脱

MySQL での外部キーの実装は、次の重要な点で SQL 標準と異なります。

同じ参照キー値を持つ複数の行が親テーブルにある場合、InnoDB は、同じキー値を持つほかの親の行が存在しないかのように、外部キーチェックで動作します。たとえば、RESTRICT 型の制約が定義されていて、複数の親の行を含む子の行が存在する場合は、これらの親の行のいずれかを削除することが InnoDB で許可されません。

InnoDB では、外部キー制約に対応するインデックス内のレコードに基づいて、深さ優先アルゴリズムを使用したカスケード操作が実行されます。
UNIQUE でないキーを参照する FOREIGN KEY 制約は、標準 SQL ではなく InnoDB の拡張機能です。
ON UPDATE CASCADE または ON UPDATE SET NULL が同じカスケード中に以前に更新していた同じテーブルを更新するように再帰する場合、RESTRICT のように機能します。つまり、自己参照型 ON UPDATE CASCADE または ON UPDATE SET NULL 操作は使用できません。この目的は、カスケード更新で発生する無限ループを回避することです。反対に、自己参照型 ON DELETE SET NULL は、自己参照型 ON DELETE CASCADE と同様に動作できます。カスケード操作は、15 レベルよりも深くネストされる可能性がありません。
多くの行を挿入、削除、または更新する SQL ステートメントでは、外部キー制約 (一意の制約など) が行ごとにチェックされます。外部キーチェックを行なっているとき、InnoDB は、調べる必要のある子レコードまたは親レコード上に共有行レベルロックを設定します。MySQL は外部キー制約の確認を即座に行います。その確認がトランザクションコミットまで遅延されることはありません。SQL 標準によると、デフォルトの動作は遅延チェックにするべきです。つまり、SQL ステートメント全体が処理されたあとにはじめて、制約がチェックされます。これは、外部キーを使用して自己参照する行を削除できないことを意味します。

InnoDB 外部キーが SQL 標準とどのように異なるかについては、セクション14.6.6「InnoDB と FOREIGN KEY 制約」を参照してください。

PREV HOME UP NEXT

User Comments

Posted by sukasom chaiyakul on April 8, 2005

CREATE TABLE reviewing
(
reviewingid INTEGER ,
papername text,
reviewername text,
PRIMARY KEY(reviewingid)
);

CREATE TABLE score
(
reviewingid integer,
questionnumber integer,
score integer,
FOREIGN KEY (reviewingid) REFERENCES reviewing (reviewingid) on delete cascade
);

insert into reviewing values (1,"how to survive","John Doe") ;
insert into score values(1,1,5);
delete from reviewing where reviewingid = 1;

Insert the record in the table reviewing, and then insert the record into the table score which reviewingid in table score is referenced to reviewingid in reviewing table.

When deleting the record in the reviewing table, the record in the score with the same reviewerid is also deleted as well, because of the "on delete cascade" clause.

Posted by Clement Soullard on July 25, 2006

But you can also use the simpler definition

-- Posts table
CREATE TABLE post(
id INT PRIMARY KEY,
title VARCHAR(100),
dateissued TIMESTAMP,
datecreated TIMESTAMP,
datemodified TIMESTAMP,
content TEXT,
);

-- Date of publication
CREATE TABLE publish(
id INT,
datepublish TIMESTAMP,
urldest VARCAHR(100),
postid int REFERENCES post(id)
);

Posted by Joe Brown on September 3, 2013

Disable Foreign Key Checks is actually explained in section 13.1.14.2, Using FOREIGN KEY Constraints: http://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html

mysql> SET foreign_key_checks = 0;
mysql> SOURCE dump_file_name;
mysql> SET foreign_key_checks = 1;