Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 26.8Mb
PDF (A4) - 26.9Mb
HTML Download (TGZ) - 7.1Mb
HTML Download (Zip) - 7.2Mb


14.6.6 InnoDB と FOREIGN KEY 制約

このセクションでは、InnoDB ストレージエンジンでの外部キー処理と、MySQL サーバーでの処理とを比較したときの相違点について説明します。

外部キーの定義

InnoDB テーブルの外部キー定義は、次のような条件の対象となります。

  • InnoDB では、外部キーが任意のインデックスカラムまたはカラムのグループを参照することが許可されます。ただし、参照されるテーブルには、参照されるカラムが同じ順序で最初のカラムとして一覧表示されているインデックスが存在する必要があります。

  • 現在、InnoDB ではユーザー定義のパーティションを持つテーブルの外部キーがサポートされていません。つまり、ユーザーがパーティション化した InnoDB テーブルには、外部キーで参照される外部キー参照またはカラムが含まれる可能性がありません。

  • InnoDB では、外部キー制約が一意でないキーを参照することが許可されます。これは、標準 SQL の InnoDB 拡張です。

参照アクション

InnoDB テーブルの外部キーに関する参照アクションは、次のような条件の対象となります。

  • SET DEFAULT は、MySQL サーバーで許可されていますが、InnoDB では無効として拒否されます。この句を使用した CREATE TABLE および ALTER TABLE ステートメントは、InnoDB テーブルで許可されていません。

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

  • InnoDB では、外部キー制約に対応するインデックス内のレコードに基づいて、深さ優先アルゴリズムを使用したカスケード操作が実行されます。

  • ON UPDATE CASCADE または ON UPDATE SET NULL は、カスケード中に以前に更新していた同じテーブルを更新するように再帰する場合、RESTRICT と同様に機能します。つまり、自己参照型 ON UPDATE CASCADE または ON UPDATE SET NULL 操作は使用できません。この目的は、カスケード更新で発生する無限ループを回避することです。反対に、自己参照型 ON DELETE SET NULL は、自己参照型 ON DELETE CASCADE と同様に動作できます。カスケード操作は、15 レベルよりも深くネストされる可能性がありません。

  • 一般的な MySQL と同様に、多数の行を挿入、削除、または更新する SQL ステートメントでは、InnoDB によって UNIQUE および FOREIGN KEY 制約が 1 行ずつチェックされます。外部キーチェックの実行時に、InnoDB は、調査対象の子または親のレコード上に共有の行レベルロックを設定します。InnoDB では、即座に外部キー制約がチェックされ、そのチェックはトランザクションのコミットまで遅延されません。SQL 標準によると、デフォルトの動作は遅延チェックにするべきです。つまり、SQL ステートメント全体が処理されたあとにはじめて、制約がチェックされます。InnoDB で制約の遅延チェックが実装されるまで、外部キーを使用してそれ自体を参照するレコードを削除するなどの一部の操作が実行できません。

外部キーの使用法とエラー情報

外部キーおよびそれらの使用法に関する一般的な情報は、INFORMATION_SCHEMA.KEY_COLUMN_USAGE テーブルでクエリーを実行することで取得できます。InnoDB テーブルに固有の詳細な情報は、INNODB_SYS_FOREIGN および INNODB_SYS_FOREIGN_COLS テーブル、または INFORMATION_SCHEMA データベースで見つかります。セクション13.1.17.2「外部キー制約の使用」も参照してください。

SHOW ERRORS 以外でも、InnoDB テーブルが関与する外部キーエラー (通常、MySQL サーバーではエラー 150) の発生時に、SHOW ENGINE INNODB STATUS の出力をチェックすることで、最近の InnoDB 外部キーエラーの詳細な説明を取得できます。


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Christopher Beland on April 11, 2011
Note that it will fail if you try to insert data into a table that has a foreign key constraint where the foreign table is a view.
  Posted by David Filmer on June 5, 2011
If you get an error:
<b>ERROR 1216: Cannot add or update a child row: a foreign key constraint fails</b>
it means you have two tables, at least one of which contains data, and you are trying to establish a relationship between keys (such as table1.id = table2.id), but there is data in one or both tables which does not currently meet this condition (often because table1 might contain keys that table2 does not contain and/or vice-versa).
You can't force a relationship onto tables which are not already compliant with the constraint (nor would you want to - if you are trying to do this, you haven't thought it out).

  Posted by manish patel on July 12, 2011
========To Remove Foreign Key from child table==========

CREATE TABLE parent (id INT NOT NULL, name varchar(25) not null default '',
PRIMARY KEY `id_name` (id,name)
) ENGINE=INNODB;

CREATE TABLE child (id INT, parent_id INT, parent_name varchar(25) not null default '',
INDEX par_ind (parent_id),
FOREIGN KEY `id_name` (parent_id, parent_name) REFERENCES parent(id,name)
ON Delete CASCADE
on update CASCADE

) ENGINE=INNODB;

#show create table schema of child table
show create table child;

CREATE TABLE `child` (
`id` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
`parent_name` varchar(25) NOT NULL DEFAULT '',
KEY `par_ind` (`parent_id`),
KEY `id_name` (`parent_id`,`parent_name`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`, `parent_name`) REFERENCES `parent` (`id`, `name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

then use CONSTRAINT `child_ibfk_1` to drop as Foreign key from child table by using below query.

alter table child drop FOREIGN KEY child_ibfk_1;

  Posted by Markus Zeller on January 5, 2012
I had the same issue and it could be solved by checking the keys to be *exactly* the same.

In my case I had the parent table with int unsigned not null, and the child had int signed not null.

So the signing was causing the error. I changed the child to be unsigned (just a unwanted mistake on creating the table) and all went fine.
  Posted by Philip Flammer on June 18, 2012
When you get errors like the errno 150, and errno 121, and you don't have SUPER privileges to run SHOW ENGINE INNODB STATUS, it can take a long time to debug as a number of people above have discussed. Here is a list of known causes and solutions to various foreign key errors including these:

http://eliacom.com/wpErrNo150.php
  Posted by Aurelien Marchand on February 27, 2013
INSERT IGNORE will *still* trip the foreign key constraint. It's a known bug that is to be fixed in a later version.

CREATE TABLE T1 (id1 int, index key(id1));
CREATE TABLE T2(id2 int, foreign key (id2) references T1(id1));

INSERT INTO T1(1); /* OK */
START TRANSACTION;
INSERT IGNORE INTO T2(2); /* MySQL error, foreign key constraint violated, even though it was marked as IGNORE */
INSERT IGNORE INTO T2(1);
COMMIT;

end result:
T1 contains (1)
T2 is empty!
Sign Up Login You must be logged in to post a comment.