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


13.1.17.2 外部キー制約の使用

MySQL は、関連データのテーブルにまたがる相互参照を可能にする外部キーと、この分散したデータの整合性を維持するために役立つ外部キー制約をサポートします。CREATE TABLE または 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

index_name は、外部キー ID を表します。外部キーをサポートできる子テーブル上に明示的に定義されたインデックスがすでに存在する場合、index_name 値は無視されます。それ以外の場合、MySQL は、次のルールに従って名前が付けられた外部キーのインデックスを暗黙的に作成します。

  • 定義されている場合は、CONSTRAINT symbol 値が使用されます。それ以外の場合は、FOREIGN KEY index_name 値が使用されます。

  • CONSTRAINT symbolFOREIGN KEY index_name のどちらも定義されていない場合、外部キーのインデックス名は、参照している外部キーカラムの名前を使用して生成されます。

外部キー定義は、次の条件に従います。

  • 外部キー関係には、中央のデータ値を保持している親テーブルと、その元の親を指す同一の値を持っている子テーブルが含まれます。FOREIGN KEY 句は、子テーブルで指定されます。親テーブルと子テーブルは、同じストレージエンジンを使用する必要があります。これらは、TEMPORARY テーブルであってはいけません。

  • 外部キー内の対応するカラムと、参照されるキーは同様のデータ型を持っている必要があります。整数型のサイズと符号が同じである必要があります。文字列型の長さが同じである必要はありません。バイナリ以外の (文字の) 文字列カラムの場合、文字セットと照合順序が同じである必要があります。

  • MySQL では、外部キーチェックを高速に実行でき、かつテーブルスキャンが必要なくなるように、外部キーおよび参照されるキーに関するインデックスが必要です。参照しているテーブルには、外部キーカラムが同じ順序で最初のカラムとしてリストされているインデックスが存在する必要があります。このようなインデックスが存在しない場合は、参照しているテーブル上に自動的に作成されます。このインデックスは、外部キー制約を適用するために使用できる別のインデックスを作成した場合、あとで暗黙のうちに削除される可能性があります。index_name (指定されている場合) は、前に説明したとおりに使用されます。

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

    NDB には、外部キーとして参照されるいずれかのカラム上の明示的な一意のキー (または主キー) が必要です。

  • 外部キーカラム上のインデックスプリフィクスはサポートされていません。この 1 つの影響として、BLOB および TEXT カラム上のインデックスには常にプリフィクス長が含まれている必要があるため、それらのカラムを外部キーに含めることができない点があります。

  • CONSTRAINT symbol 句が指定されている場合、symbol 値 (使用されている場合) はデータベース内で一意である必要があります。symbol が重複している場合は、次のようなエラーが発生します: ERROR 1022 (2300): 書き込めません。テーブル '#sql- 464_1' に重複するキーがあります'。この句が指定されていない場合や、CONSTRAINT キーワードのあとに symbol が含まれていない場合は、制約の名前が自動的に作成されます。

  • 現在、InnoDB ではユーザー定義のパーティションを持つテーブルの外部キーがサポートされていません。これには、親テーブルと子テーブルの両方が含まれます。

    この制限は、KEY または LINEAR KEY によってパーティション化された NDB テーブル (NDB ストレージエンジンによってサポートされる唯一のユーザーパーティショニングタイプ) には適用されません。これらは外部キー参照を含むか、またはこのような参照のターゲットになることができます。

  • NDB テーブルでは、参照先が親テーブルの主キーである場合、ON UPDATE CASCADE はサポートされません。

参照アクション

このセクションでは、外部キーが参照整合性の保証にどのように役立つかについて説明します。

外部キーをサポートするストレージエンジンで、親テーブル内に一致する候補のキー値が存在しない場合、MySQL は、子テーブル内に外部キー値を作成しようとするすべての INSERT または UPDATE 操作を拒否します。

UPDATE または DELETE 操作が、子テーブル内に一致する行を持つ親テーブル内のキー値に影響を与える場合、その結果は、FOREIGN KEY 句の ON UPDATE および ON DELETE サブ句を使用して指定された参照アクションによって異なります。MySQL は、実行されるアクションに関連した次の 5 つのオプションをサポートしています。

  • CASCADE: 親テーブルの行を削除または更新し、子テーブル内の一致する行を自動的に削除または更新します。ON DELETE CASCADEON UPDATE CASCADE の両方がサポートされています。2 つのテーブル間で、親テーブルまたは子テーブル内の同じカラムに対して機能する複数の ON UPDATE CASCADE 句を定義しないでください。

    注記

    現在、カスケードされた外部キーのアクションではトリガーがアクティブになっていません。

  • SET NULL: 親テーブルの行を削除または更新し、子テーブル内の 1 つまたは複数の外部キーカラムを NULL に設定します。ON DELETE SET NULL 句と ON UPDATE SET NULL 句の両方がサポートされています。

    SET NULL アクションを指定する場合は、子テーブル内のカラムを NOT NULL として宣言していないことを確認してください

  • RESTRICT: 親テーブルに対する削除または更新操作を拒否します。RESTRICT (または NO ACTION) を指定することは、ON DELETE または ON UPDATE 句を省略することと同じです。

  • NO ACTION: 標準 SQL のキーワード。MySQL では、RESTRICT と同等です。MySQL Server は、参照されるテーブル内に関連する外部キー値が存在する場合、親テーブルに対する削除または更新操作を拒否します。一部のデータベースシステムは遅延チェックを備えており、その場合、NO ACTION は遅延チェックです。MySQL では、外部キー制約はただちにチェックされるため、NO ACTIONRESTRICT と同じです。

  • SET DEFAULT: このアクションは MySQL パーサーによって認識されますが、InnoDBNDB はどちらも、ON DELETE SET DEFAULT または ON UPDATE SET DEFAULT 句を含むテーブル定義を拒否します。

指定されていない ON DELETE または ON UPDATE では、デフォルトのアクションは常に RESTRICT です。

MySQL は、1 つのテーブル内のあるカラムと別のカラムの間の外部キー参照をサポートしています。(あるカラムが、それ自体への外部キー参照を持つことはできません。)これらの場合、子テーブルのレコードは、実際に同じテーブル内の依存レコードを参照します。

外部キー句の例

単一カラム外部キーを使用して parent および child テーブルを関連付ける単純な例を次に示します。

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT, 
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id) 
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

product_order テーブルにほかの 2 つのテーブルへの外部キーが存在する、より複雑な例。1 つの外部キーが、product テーブル内の 2 カラムのインデックスを参照しています。もう一方の外部キーは、customer テーブル内の単一カラムインデックスを参照しています。

CREATE TABLE product (
    category INT NOT NULL, id INT NOT NULL,
    price DECIMAL,
    PRIMARY KEY(category, id)
)   ENGINE=INNODB;

CREATE TABLE customer (
    id INT NOT NULL,
    PRIMARY KEY (id)
)   ENGINE=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),
    INDEX (customer_id),

    FOREIGN KEY (product_category, product_id)
      REFERENCES product(category, id)
      ON UPDATE CASCADE ON DELETE RESTRICT,

    FOREIGN KEY (customer_id)
      REFERENCES customer(id)
)   ENGINE=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]

外部キーは、自己参照型にする (同じテーブルを参照する) ことができます。ALTER TABLE を使用してテーブルに外部キー制約を追加する場合は、まず必要なインデックスを作成することを忘れないでください。

外部キーの削除

次に示す構文を使用して、ALTER TABLE で外部キーを削除することもできます。

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

外部キーを作成したときに FOREIGN KEY 句に CONSTRAINT の名前が含まれていた場合は、その名前を参照して外部キーを削除できます。それ以外の場合は、外部キーが作成されるときに fk_symbol 値が内部的に生成されます。外部キーを削除するときにシンボル値を見つけるには、次に示すように、SHOW CREATE TABLE ステートメントを使用します。

mysql> SHOW CREATE TABLE ibtest11c\G
*************************** 1. row ***************************
       Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
  `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 `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARSET=latin1
1 row in set (0.01 sec)

mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`;

MySQL 5.6.6 より前は、同じ ALTER TABLE ステートメントでの外部キーの追加と削除は、問題が発生する場合があるためサポートされていません。操作ごとに個別のステートメントを使用するようにしてください。MySQL 5.6.6 の時点では、同じ ALTER TABLE ステートメントでの外部キーの追加と削除は ALTER TABLE ... ALGORITHM=INPLACE ではサポートされますが、ALTER TABLE ... ALGORITHM=COPY では未サポートのままです。

MySQL 5.6.7 より前は、ALTER TABLE を使用して外部キーカラムの定義を変更すると、参照整合性が失われる可能性がありました。たとえば、NULL 値を含む外部キーカラムを NOT NULL になるように変更すると、NULL 値が空の文字列になりました。同様に、親テーブル内の行を削除する ALTER TABLE IGNORE によって、参照整合性が破壊される可能性がありました。

5.6.7 の時点では、参照整合性が失われる可能性のある外部キーカラムへの変更がサーバーによって禁止されます。回避方法として、カラム定義を変更する前に ALTER TABLE ... DROP FOREIGN KEY を使用し、あとで ALTER TABLE ... ADD FOREIGN KEY を使用します。

外部キーおよびその他の MySQL ステートメント

FOREIGN KEY ... REFERENCES ... 句内のテーブルとカラムの識別子は、逆引用符 (`) で囲むことができます。あるいは、ANSI_QUOTES SQL モードが有効になっている場合は、二重引用符 (") を使用できます。また、lower_case_table_names システム変数の設定も考慮に入れられます。

SHOW CREATE TABLE ステートメントの出力の一部として、子テーブルの外部キー定義を表示できます。

SHOW CREATE TABLE tbl_name;

INFORMATION_SCHEMA.KEY_COLUMN_USAGE テーブルをクエリーすることによって、外部キーに関する情報を取得することもできます。

INNODB_SYS_FOREIGN および INNODB_SYS_FOREIGN_COLS テーブル、さらには INFORMATION_SCHEMA データベース内の InnoDB テーブルによって使用される外部キーに関する情報を検索できます。

mysqldump は、テーブルの正しい定義 (子テーブルへの外部キーを含む) をダンプファイル内に生成します。

外部キー関係を持つテーブルのダンプファイルのリロードを容易にするために、mysqldump は、foreign_key_checks を 0 に設定するステートメントをダンプ出力内に自動的に含めます。これにより、ダンプがリロードされるときに特定の順序でリロードする必要のあるテーブルに関する問題が回避されます。また、この変数を手動で設定することもできます。

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

これにより、外部キーに関して正しく順序付けられていないテーブルがダンプファイルに含まれている場合でも、そのテーブルを任意の順序でインポートできます。また、インポート操作も高速化されます。foreign_key_checks を 0 に設定することは、LOAD DATA および ALTER TABLE 操作中に外部キー制約を無視するためにも役立つ場合があります。ただし、foreign_key_checks = 0 の場合でも、MySQL では、カラムが一致しないカラム型を参照している外部キー制約の作成は許可されません。また、テーブルに外部キー制約が存在する場合は、ALTER TABLE を使用して、そのテーブルを別のストレージエンジンを使用するように変更することはできません。ストレージエンジンを変更するには、まず外部キー制約をすべて削除する必要があります。

SET foreign_key_checks = 0 を実行しないかぎり、FOREIGN KEY 制約によって参照されるテーブルに対して DROP TABLE を発行できません。テーブルを削除すると、そのテーブルを作成するために使用されたステートメントで定義されていた制約もすべて削除されます。

削除されたテーブルを再作成する場合は、そのテーブルに、それを参照している外部キー制約に準拠する定義が存在する必要があります。また、カラムの正しい名前と型、および前に説明した参照されるキーに関するインデックスが存在する必要があります。これらが満たされていない場合、MySQL はエラー 1005 を返し、エラーメッセージでエラー 150 を示します。これは、外部キー制約が正しく形成されなかったことを示します。同様に、ALTER TABLE がエラー 150 で失敗した場合、これは、変更されたテーブルのために外部キー定義が誤って形成されることを示します。

InnoDB テーブルの場合は、SHOW ENGINE INNODB STATUS の出力をチェックすることによって、MySQL Server で最新の InnoDB 外部キーエラーの詳細な説明を取得できます。

重要

ANSI/ISO SQL 標準に精通しているユーザーの場合は、参照整合性の制約定義で使用される MATCH 句を認識または適用するストレージエンジンは (InnoDB を含め) 存在しません。明示的な MATCH 句を使用しても、指定された効果が得られないだけでなく、ON DELETE および ON UPDATE 句が無視される原因にもなります。これらの理由により、MATCH の指定は避けるようにしてください。

SQL 標準での MATCH 句は、複合 (マルチカラム) 外部キー内の NULL 値が、主キーとの比較時にどのように処理されるかを制御します。MySQL は基本的に、外部キーをすべてまたは部分的に NULL にすることが許可される、MATCH SIMPLE で定義されるセマンティクスを実装しています。その場合は、このような外部キーを含む (子テーブルの) 行の挿入が許可され、その行は参照される (親) テーブル内のどの行にも一致しません。トリガーを使用して、ほかのセマンティクスを実装できます。

さらに、MySQL ではパフォーマンス上の理由から、参照されるカラムにインデックスを設定する必要があります。ただし、システムでは、参照されるカラムを UNIQUE にするか、または NOT NULL として宣言するという要件は適用されません。一意でないキーまたは NULL 値を含むキーへの外部キー参照の処理は、UPDATEDELETE CASCADE などの操作に対して適切に定義されていません。UNIQUE (PRIMARY を含む) および NOT NULL キーのみを参照する外部キーを使用することをお勧めします。

さらに、MySQL は、参照がカラム指定の一部として定義されている (SQL 標準で定義された) インラインの REFERENCES 指定を認識せず、またサポートもしていません。MySQL は、個別の FOREIGN KEY 指定の一部として指定されている場合にのみ REFERENCES 句を受け入れます。外部キーをサポートしていない (MyISAM などの) ストレージエンジンの場合、MySQL Server は、外部キーの指定を解析して無視します。


User Comments
  Posted by Roberto Novakosky on March 18, 2015
Take care when 'REPLACE INTO', it causes automatic DELETE on CASCADE records. To resolve it, is possible:
SET FOREIGN_KEY_CHEKS = 0;
REPLACE INTO ...
SET FOREINGN KEY_CHEKS = 1;

or instead 'REPLACE INTO..' use only 'UPDATE...' and INSERT INTO when necessary.
Sign Up Login You must be logged in to post a comment.