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

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

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

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

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

外部キーをサポートするストレージエンジンでは、親テーブルに一致する候補キー値がない場合、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 は、外部キー制約に対応するインデックスのレコードに対して、深さ優先検索アルゴリズムを使用してカスケード操作を実行します。

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

格納された生成カラムのベースカラムに対する外部キー制約では、CASCADE、SET NULL または SET DEFAULT を ON 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 が無効な場合、外部キー制約は無視されますが、次の例外があります:

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;;
...