Documentation Home
MySQL 8.0 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 36.1Mb
PDF (A4) - 36.2Mb


このページは機械翻訳したものです。

13.1.20.5 FOREIGN KEY の制約

MySQL では、テーブル間の相互参照関連データを許可する外部キー、および関連データの一貫性を保つための外部キー制約がサポートされています。

外部キー関係には、初期カラム値を保持する親テーブルと、親カラム値を参照するカラム値を持つ子テーブルが含まれます。 子テーブルに外部キー制約が定義されています。

CREATE TABLE ステートメントまたは ALTER TABLE ステートメントで外部キー制約を定義するために不可欠な構文は次のとおりです:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

外部キー制約の使用方法については、このセクションの次のトピックで説明します:

識別子

外部キー制約のネーミングは、次のルールによって制御されます:

  • CONSTRAINT symbol 値が使用されます (定義されている場合)。

  • CONSTRAINT symbol 句が定義されていない場合、または CONSTRAINT キーワードの後に記号が含まれていない場合は、制約名が自動的に生成されます。

    MySQL 8.0.16 より前では、CONSTRAINT symbol 句が定義されていなかった場合、または CONSTRAINT キーワードのあとにシンボルが含まれていなかった場合、InnoDBNDB の両方のストレージエンジンは FOREIGN_KEY index_name を使用します (定義されている場合)。 MySQL 8.0.16 以上では、FOREIGN_KEY index_name は無視されます。

  • 定義されている場合、CONSTRAINT symbol 値はデータベース内で一意である必要があります。 symbol が重複すると、次のようなエラーが発生: ERROR 1005 (HY000): テーブル'test.fk1'を作成できません (errno: 121)

  • NDB Cluster は、外部名を作成時と同じ大文字/小文字を使用して格納します。 8.0.20 より前のバージョンでは、SELECT およびその他の SQL ステートメントを処理する際に、NDB は、lower_case_table_names が 0 に等しい場合に、そのようなステートメントの外部キーの名前を大/小文字を区別して格納された名前と比較していました。 NDB 8.0.20 以降では、この値はこのような比較の方法に影響を与えなくなり、大文字と小文字に関係なく常に実行されます。 (Bug #30512043)

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

条件と制限

外部キー制約には、次の条件および制限事項があります:

  • 親テーブルと子テーブルは同じストレージエンジンを使用する必要があり、一時テーブルとして定義することはできません。

  • 外部キー制約を作成するには、親テーブルに対する REFERENCES 権限が必要です。

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

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

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

  • InnoDB では、外部キーが任意のインデックスカラムまたはカラムのグループを参照することが許可されます。 ただし、参照テーブルには、参照カラムが同じ順序の first カラムであるインデックスが必要です。 InnoDB がインデックスに追加する非表示カラムも考慮されます (セクション15.6.2.1「クラスタインデックスとセカンダリインデックス」 を参照)。

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

  • 外部キーカラム上のインデックスプリフィクスはサポートされていません。 したがって、BLOB カラムおよび TEXT カラムは、常に接頭辞の長さを含む必要があるため、外部キーに含めることはできません。

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

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

  • 外部キー関係のテーブルは、別のストレージエンジンを使用するように変更できません。 ストレージエンジンを変更するには、まず外部キー制約をすべて削除する必要があります。

  • 外部キー制約は、仮想生成カラムを参照できません。

外部キー制約の MySQL 実装と SQL 標準の違いの詳細は、セクション1.7.2.3「FOREIGN KEY 制約の違い」 を参照してください。

参照アクション

UPDATE または DELETE 操作が、子テーブルで一致する行を持つ親テーブルのキー値に影響する場合、結果は FOREIGN KEY 句の ON UPDATE および ON DELETE 副次句で指定された参照アクションによって異なります。 参照アクションには次のものがあります:

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

    外部キーリレーションシップの両方のテーブルに FOREIGN KEY 句が定義されている場合、カスケード操作を成功させるには、一方の FOREIGN KEY 句に定義されている ON UPDATE CASCADE または ON DELETE CASCADE 副次句をもう一方の FOREIGN KEY 句に定義する必要があります。 ON UPDATE CASCADE または ON DELETE CASCADE 副次句が FOREIGN KEY 句に対してのみ定義されている場合、カスケード操作はエラーで失敗します。

    注記

    カスケードされた外部キーアクションはトリガーをアクティブ化しません。

  • SET NULL: 親テーブルから行を削除または更新し、子テーブルの外部キーカラムを 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 句を含むテーブル定義を拒否します。

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

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

デフォルトでは、明示的に指定された ON DELETE NO ACTION または ON UPDATE NO ACTION 句は、SHOW CREATE TABLE 出力または mysqldump でダンプされたテーブルには表示されません。 同等のデフォルト以外のキーワードである RESTRICT は、SHOW CREATE TABLE 出力および mysqldump でダンプされたテーブルに表示されます。

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

NDB 8.0.16 の時点: NDB テーブルの場合、子テーブルに TEXT 型または BLOB 型のいずれかのカラムが含まれる ON DELETE CASCADE はサポートされません。 (Bug #89511、Bug #27484882)

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

格納された生成カラムに対する外部キー制約では、CASCADESET NULL または SET DEFAULTON UPDATE 参照アクションとして使用することも、SET NULL または SET DEFAULTON DELETE 参照アクションとして使用することもできません。

格納された生成カラムのベースカラムに対する外部キー制約では、CASCADESET NULL または SET DEFAULTON UPDATE または ON DELETE の参照アクションとして使用できません。

外部キー制約の例

次の簡単な例では、単一カラムの外部キーを使用して 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] (col_name, ...)
    REFERENCES tbl_name (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 名が定義されていた場合は、その名前を参照して外部キー制約を削除できます。 それ以外の場合は、制約名が内部的に生成されているため、その値を使用する必要があります。 外部キー制約名を確認するには、SHOW CREATE TABLE を使用します:

mysql> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
  REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysql> ALTER TABLE child DROP FOREIGN KEY `child_ibfk_1`;

ALTER TABLE ... ALGORITHM=INPLACE では、同じ ALTER TABLE ステートメントでの外部キーの追加および削除がサポートされています。 ALTER TABLE ... ALGORITHM=COPY ではサポートされていません。

外部キーチェック

外部キーチェックは、デフォルトで有効になっている foreign_key_checks 変数によって制御されます。 通常、この変数は通常の操作中は有効のままにして、参照整合性を強制します。 foreign_key_checks 変数は、InnoDB テーブルの場合と同じ効果を NDB テーブルに与えます。

foreign_key_checks 変数は動的であり、グローバルスコープとセッションスコープの両方をサポートします。 システム変数の使用の詳細は、セクション5.1.9「システム変数の使用」 を参照してください。

外部キーチェックの無効化は、次の場合に役立ちます:

  • 外部キー制約によって参照されるテーブルの削除。 参照テーブルは、foreign_key_checks が無効化された後にのみ削除できます。 テーブルを削除すると、テーブルに定義されている制約も削除されます。

  • 外部キー関係に必要な順序とは異なる順序でテーブルをリロードします。 たとえば、mysqldump では、子テーブルの外部キー制約など、ダンプファイル内のテーブルの正しい定義が生成されます。 外部キー関係を持つテーブルのダンプファイルを簡単にリロードできるように、mysqldump では、foreign_key_checks を無効にするステートメントがダンプ出力に自動的に含まれます。 これにより、ダンプファイルに外部キーに対して正しく順序付けされていないテーブルが含まれている場合に、任意の順序でテーブルをインポートできます。 foreign_key_checks を無効にすると、外部キーチェックが回避され、インポート操作も高速化されます。

  • 外部キーチェックを回避するための LOAD DATA 操作の実行。

  • 外部キー関係を持つテーブルに対する ALTER TABLE 操作の実行。

foreign_key_checks が無効な場合、外部キー制約は無視されますが、次の例外があります:

  • テーブル定義がテーブルを参照する外部キー制約に準拠していない場合、以前に削除されたテーブルを再作成するとエラーが返されます。 テーブルには正しいカラム名およびタイプが必要です。 参照キーに対するインデックスも必要です。 これらの要件が満たされない場合、MySQL は errno を参照するエラー 1005 を返します: 150:外部キー制約が正しく形成されなかったことを意味します。

  • テーブルを変更すると、エラーが返されます (errno: 150) 変更されたテーブルに対して外部キー定義が正しく構成されていない場合。

  • 外部キー制約に必要なインデックスの削除。 インデックスを削除する前に、外部キー制約を削除する必要があります。

  • カラムが一致しないカラムタイプを参照する外部キー制約の作成。

foreign_key_checks を無効にすると、次の追加の影響があります:

  • データベースの外部のテーブルによって参照される外部キーを持つテーブルを含むデータベースを削除できます。

  • 外部キーが他のテーブルによって参照されているテーブルを削除できます。

  • foreign_key_checks を有効にしてもテーブルデータのスキャンはトリガーされません。つまり、foreign_key_checks が無効になっている間にテーブルに追加された行は、foreign_key_checks が再度有効になったときに一貫性がチェックされません。

ロック中

MySQL は、必要に応じて、外部キー制約によって関連付けられたテーブルにメタデータロックを拡張します。 メタデータロックを拡張すると、競合する DML 操作および DDL 操作が関連するテーブルで同時に実行されなくなります。 この機能を使用すると、親テーブルが変更されたときに外部キーメタデータを更新することもできます。 以前の MySQL リリースでは、子テーブルが所有する外部キーメタデータは安全に更新できませんでした。

テーブルが LOCK TABLES で明示的にロックされている場合、外部キー制約に関連するテーブルはすべて暗黙的にオープンおよびロックされます。 外部キーチェックでは、関連するテーブルに対して共有読取り専用ロック (LOCK TABLES READ) が取得されます。 カスケード更新では、操作に関連する関連テーブルに対してシェアードナッシング書込みロック (LOCK TABLES WRITE) が取得されます。

外部キー定義およびメタデータ

外部キー定義を表示するには、SHOW CREATE TABLE を使用します:

mysql> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
  REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

INFORMATION_SCHEMA.KEY_COLUMN_USAGE テーブルから、外部キーに関する情報を取得できます。 このテーブルに対するクエリーの例を次に示します。

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
       FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
       WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;
+--------------+------------+-------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME |
+--------------+------------+-------------+-----------------+
| test         | child      | parent_id   | child_ibfk_1    |
+--------------+------------+-------------+-----------------+

InnoDB 外部キーに固有の情報は、INNODB_FOREIGN テーブルおよび INNODB_FOREIGN_COLS テーブルから取得できます。 クエリーの例を次に示します:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN \G
*************************** 1. row ***************************
      ID: test/child_ibfk_1
FOR_NAME: test/child
REF_NAME: test/parent
  N_COLS: 1
    TYPE: 1

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS \G
*************************** 1. row ***************************
          ID: test/child_ibfk_1
FOR_COL_NAME: parent_id
REF_COL_NAME: id
         POS: 0
外部キーエラー

InnoDB テーブルに関連する外部キーエラー (通常は MySQL Server のエラー 150) が発生した場合、SHOW ENGINE INNODB STATUS 出力をチェックすることで、最新の外部キーエラーに関する情報を取得できます。

mysql> SHOW ENGINE INNODB STATUS\G
...
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-04-12 14:57:24 0x7f97a9c91700 Transaction:
TRANSACTION 7717, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
MySQL thread id 8, OS thread handle 140289365317376, query id 14 localhost root update
INSERT INTO child VALUES (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5), (NULL, 6)
Foreign key constraint fails for table `test`.`child`:
,
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE
  CASCADE ON UPDATE CASCADE
Trying to add in child table, in index par_ind tuple:
DATA TUPLE: 2 fields;
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000003; asc     ;;

But in parent table `test`.`parent`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 000000001e19; asc       ;;
 2: len 7; hex 81000001110137; asc       7;;
...
警告

ユーザーがすべての親テーブルに対するテーブルレベルの権限を持っている場合、外部キー操作に関する ER_NO_REFERENCED_ROW_2 および ER_ROW_IS_REFERENCED_2 のエラーメッセージでは、親テーブルに関する情報が公開されます。 ユーザーがすべての親テーブルに対するテーブルレベルの権限を持っていない場合は、かわりにより一般的なエラーメッセージ (ER_NO_REFERENCED_ROW および ER_ROW_IS_REFERENCED) が表示されます。

例外として、DEFINER 権限で実行するように定義されたストアドプログラムの場合、権限が評価されるユーザーは、起動するユーザーではなく、プログラムの DEFINER 句のユーザーです。 そのユーザーがテーブルレベルの親テーブル権限を持っている場合でも、親テーブルの情報は表示されます。 この場合、ストアドプログラムの作成者は、適切な条件ハンドラを含めて情報を非表示にする必要があります。