外部キー制約
外部キーは関連データのテーブル間参照を可能にし、外部キー制約は関連データの一貫性を確保します。TiDBはv6.6.0以降、外部キーと外部キー制約をサポートしています。v8.5.0以降、この機能は一般提供となります。
外部キーは子テーブルで定義されます。構文は次のとおりです。
- ForeignKeyDef
- ReferenceOption
ForeignKeyDef
::= ( 'CONSTRAINT' Identifier )? 'FOREIGN' 'KEY'
Identifier? '(' ColumnName ( ',' ColumnName )* ')'
'REFERENCES' TableName '(' ColumnName ( ',' ColumnName )* ')'
( 'ON' 'DELETE' ReferenceOption )?
( 'ON' 'UPDATE' ReferenceOption )?
ReferenceOption
::= 'RESTRICT'
| 'CASCADE'
| 'SET' 'NULL'
| 'SET' 'DEFAULT'
| 'NO' 'ACTION'
ネーミング
外部キーの命名は、次の規則に従います。
CONSTRAINT identifierに名前が指定されている場合は、指定された名前が使用されます。CONSTRAINT identifierに名前が指定されておらず、FOREIGN KEY identifierに名前が指定されている場合は、FOREIGN KEY identifierに指定された名前が使用されます。CONSTRAINT identifierとFOREIGN KEY identifierどちらにも名前が指定されていない場合は、fk_1、fk_2、fk_3などの名前が自動的に生成されます。- 外部キー名は現在のテーブル内で一意である必要があります。一意でない場合、外部キーの作成時にエラー
ERROR 1826: Duplicate foreign key constraint name 'fk'が報告されます。
制限
外部キーを作成するときは、次の条件を満たす必要があります。
親テーブルも子テーブルも一時テーブルではありません。
ユーザーには親テーブルに対する権限
REFERENCESあります。親テーブルと子テーブルの外部キーによって参照される列は同じデータ型であり、サイズ、精度、長さ、文字セット、および照合順序が同じです。
外部キーの列はそれ自体を参照できません。
外部キーの列と参照先の親テーブルの列は同じインデックスを持ち、インデックス内の列の順序は外部キーの順序と一致しています。これは、外部キー制約のチェックを行う際に、インデックスを使用してテーブル全体のスキャンを回避するためです。
- 親テーブルに対応する外部キー インデックスがない場合、エラー
ERROR 1822: Failed to add the foreign key constraint. Missing index for constraint 'fk' in the referenced table 't'が報告されます。 - 子テーブルに対応する外部キー インデックスがない場合、外部キーと同じ名前のインデックスが自動的に作成されます。
- 親テーブルに対応する外部キー インデックスがない場合、エラー
BLOBまたはTEXTタイプの列に外部キーを作成することはサポートされていません。パーティションテーブルに外部キーを作成することはサポートされていません。
仮想生成列に外部キーを作成することはサポートされていません。
参照操作
UPDATEまたはDELETE操作が親テーブルの外部キー値に影響を与える場合、子テーブルの対応する外部キー値は、外部キー定義のON UPDATEまたはON DELETE節で定義された参照操作によって決定されます。参照操作には以下のものが含まれます。
CASCADE:UPDATEまたはDELETE操作が親テーブルに影響を及ぼす場合、子テーブル内の対応する行を自動的に更新または削除します。カスケード操作は深さ優先方式で実行されます。SET NULL:UPDATEまたはDELETE操作が親テーブルに影響する場合、子テーブルの一致する外部キー列をNULLに自動的に設定します。RESTRICT: 子テーブルに一致する行が含まれている場合、UPDATEまたはDELETE操作を拒否します。NO ACTION:RESTRICTと同じ。SET DEFAULT:RESTRICTと同じ。
親テーブルに一致する外部キー値がない場合、子テーブルでのINSERTまたはUPDATE操作は拒否されます。
外部キー定義でON DELETEまたはON UPDATE指定されていない場合、デフォルトの動作はNO ACTIONなります。
外部キーがSTORED GENERATED COLUMNに定義されている場合、 CASCADE 、 SET NULL 、およびSET DEFAULT参照はサポートされません。
外部キーの使用例
次の例では、単一列の外部キーを使用して親テーブルと子テーブルを関連付けます。
CREATE TABLE parent (
id INT KEY
);
CREATE TABLE child (
id INT,
pid INT,
INDEX idx_pid (pid),
FOREIGN KEY (pid) REFERENCES parent(id) ON DELETE CASCADE
);
以下はより複雑な例です。テーブルproduct_orderには、他の2つのテーブルを参照する外部キーが2つあります。1つの外部キーはテーブルproductの2つのインデックスを参照し、もう1つの外部キーはテーブルcustomerの1つのインデックスを参照します。
CREATE TABLE product (
category INT NOT NULL,
id INT NOT NULL,
price DECIMAL(20,10),
PRIMARY KEY(category, id)
);
CREATE TABLE customer (
id INT KEY
);
CREATE TABLE product_order (
id INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(id),
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)
);
外部キー制約を作成する
外部キー制約を作成するには、次のALTER TABLEステートメントを使用できます。
ALTER TABLE table_name
ADD [CONSTRAINT [identifier]] FOREIGN KEY
[identifier] (col_name, ...)
REFERENCES tbl_name (col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
外部キーは自己参照、つまり同じテーブルを参照することができます。1 ALTER TABLE使用してテーブルに外部キー制約を追加する場合は、まず外部キーが参照する親テーブルの列にインデックスを作成する必要があります。
外部キー制約を削除する
外部キー制約を削除するには、次のALTER TABLEステートメントを使用できます。
ALTER TABLE table_name DROP FOREIGN KEY fk_identifier;
外部キー制約の作成時に名前が付けられている場合は、その名前を参照して外部キー制約を削除できます。そうでない場合は、自動的に生成された制約名を使用して制約を削除する必要があります。外部キー名を表示するには、 SHOW CREATE TABLE使用します。
mysql> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`id` int DEFAULT NULL,
`pid` int DEFAULT NULL,
KEY `idx_pid` (`pid`),
CONSTRAINT `fk_1` FOREIGN KEY (`pid`) REFERENCES `test`.`parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
外部キー制約チェック
TiDBは外部キー制約チェックをサポートしており、これはシステム変数foreign_key_checksによって制御されます。デフォルトでは、この変数はONに設定されており、外部キー制約チェックが有効であることを意味します。この変数にはGLOBALとSESSION 2つのスコープがあります。この変数を有効のままにしておくことで、外部キー参照関係の整合性を確保できます。
外部キー制約チェックを無効にした場合の効果は次のとおりです。
- 外部キーによって参照される親テーブルを削除する場合、外部キー制約チェックが無効になっている場合にのみ削除が成功します。
- データベースにデータをインポートする際、テーブルの作成順序が外部キーの依存関係の順序と異なる場合があり、テーブルの作成に失敗する可能性があります。外部キー制約のチェックを無効にした場合のみ、テーブルを正常に作成できます。また、外部キー制約のチェックを無効にすると、データのインポート速度が向上します。
- データベースにデータをインポートする際、子テーブルのデータを先にインポートするとエラーが発生します。子テーブルのデータを正常にインポートするには、外部キー制約チェックを無効にしてください。
- 実行される操作
ALTER TABLE外部キーの変更が含まれる場合、この操作は外部キー制約チェックが無効になっている場合にのみ成功します。
外部キー制約チェックが無効になっている場合、次のシナリオを除き、外部キー制約チェックと参照操作は実行されません。
ALTER TABLEの実行によって外部キーの定義が間違ってしまう可能性がある場合は、実行中にエラーが報告されます。- 外部キーに必要なインデックスを削除する場合は、まず外部キーを削除する必要があります。そうしないと、エラーが報告されます。
- 外部キーを作成したが、関連する条件または制限を満たしていない場合は、エラーが報告されます。
ロック
子テーブルがINSERTまたはUPDATE場合、外部キー制約は対応する外部キー値が親テーブルに存在するかどうかを確認し、外部キー制約に違反する他の操作によって外部キー値が削除されるのを防ぐため、親テーブルの行をロックします。このロック動作は、親テーブルで外部キー値が配置されている行に対してSELECT FOR UPDATE操作を実行するのと同等です。
TiDBは現在LOCK IN SHARE MODEサポートしていないため、子テーブルが大量の同時書き込みを受け付け、参照される外部キー値のほとんどが同じ場合、深刻なロック競合が発生する可能性があります。子テーブルに大量のデータを書き込む場合は、 foreign_key_checks無効にすることをお勧めします。
外部キーの定義とメタデータ
外部キー制約の定義を表示するには、次のSHOW CREATE TABLEステートメントを実行します。
mysql> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`id` int DEFAULT NULL,
`pid` int DEFAULT NULL,
KEY `idx_pid` (`pid`),
CONSTRAINT `fk_1` FOREIGN KEY (`pid`) REFERENCES `test`.`parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
次のいずれかのシステム テーブルを使用して、外部キーに関する情報を取得することもできます。
INFORMATION_SCHEMA.KEY_COLUMN_USAGEINFORMATION_SCHEMA.TABLE_CONSTRAINTSINFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
以下に例を示します。
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 | pid | fk_1 |
| test | product_order | product_category | fk_1 |
| test | product_order | product_id | fk_1 |
| test | product_order | customer_id | fk_2 |
+--------------+---------------+------------------+-----------------+
INFORMATION_SCHEMA.TABLE_CONSTRAINTSシステム テーブルから外部キーに関する情報を取得します。
mysql> SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'\G
***************************[ 1. row ]***************************
CONSTRAINT_CATALOG | def
CONSTRAINT_SCHEMA | test
CONSTRAINT_NAME | fk_1
TABLE_SCHEMA | test
TABLE_NAME | child
CONSTRAINT_TYPE | FOREIGN KEY
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTSシステム テーブルから外部キーに関する情報を取得します。
mysql> SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS\G
***************************[ 1. row ]***************************
CONSTRAINT_CATALOG | def
CONSTRAINT_SCHEMA | test
CONSTRAINT_NAME | fk_1
UNIQUE_CONSTRAINT_CATALOG | def
UNIQUE_CONSTRAINT_SCHEMA | test
UNIQUE_CONSTRAINT_NAME | PRIMARY
MATCH_OPTION | NONE
UPDATE_RULE | NO ACTION
DELETE_RULE | CASCADE
TABLE_NAME | child
REFERENCED_TABLE_NAME | parent
外部キーを使用した実行プランのビュー
EXPLAINステートメントを使用して実行プランを表示できます。3 Foreign_Key_Check演算子は、実行されるDMLステートメントの外部キー制約チェックを実行します。
mysql> explain insert into child values (1,1);
+-----------------------+---------+------+---------------+-------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------+---------+------+---------------+-------------------------------+
| Insert_1 | N/A | root | | N/A |
| └─Foreign_Key_Check_3 | 0.00 | root | table:parent | foreign_key:fk_1, check_exist |
+-----------------------+---------+------+---------------+-------------------------------+
EXPLAIN ANALYZEステートメントを使用すると、外部キー参照の動作の実行状況を確認できます。3 演算子Foreign_Key_Cascade 、実行される DML ステートメントの外部キー参照を実行します。
mysql> explain analyze delete from parent where id = 1;
+----------------------------------+---------+---------+-----------+---------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+---------+---------+-----------+---------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+-----------+------+
| Delete_2 | N/A | 0 | root | | time:117.3µs, loops:1 | N/A | 380 Bytes | N/A |
| ├─Point_Get_1 | 1.00 | 1 | root | table:parent | time:63.6µs, loops:2, Get:{num_rpc:1, total_time:29.9µs} | handle:1 | N/A | N/A |
| └─Foreign_Key_Cascade_3 | 0.00 | 0 | root | table:child, index:idx_pid | total:1.28ms, foreign_keys:1 | foreign_key:fk_1, on_delete:CASCADE | N/A | N/A |
| └─Delete_7 | N/A | 0 | root | | time:904.8µs, loops:1 | N/A | 1.11 KB | N/A |
| └─IndexLookUp_11 | 10.00 | 1 | root | | time:869.5µs, loops:2, index_task: {total_time: 371.1µs, fetch_handle: 357.3µs, build: 1.25µs, wait: 12.5µs}, table_task: {total_time: 382.6µs, num: 1, concurrency: 5} | | 9.13 KB | N/A |
| ├─IndexRangeScan_9(Build) | 10.00 | 1 | cop[tikv] | table:child, index:idx_pid(pid) | time:351.2µs, loops:3, cop_task: {num: 1, max: 282.3µs, proc_keys: 0, rpc_num: 1, rpc_time: 263µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:220.2µs, loops:0} | range:[1,1], keep order:false, stats:pseudo | N/A | N/A |
| └─TableRowIDScan_10(Probe) | 10.00 | 1 | cop[tikv] | table:child | time:223.9µs, loops:2, cop_task: {num: 1, max: 168.8µs, proc_keys: 0, rpc_num: 1, rpc_time: 154.5µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:145.6µs, loops:0} | keep order:false, stats:pseudo | N/A | N/A |
+----------------------------------+---------+---------+-----------+---------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+-----------+------+
互換性
TiDBバージョン間の互換性
v6.6.0より前のTiDBでは、外部キーを作成する構文がサポートされていますが、作成された外部キーは無効です。v6.6.0より前に作成されたTiDBクラスターをv6.6.0以降にアップグレードした場合、アップグレード前に作成された外部キーは無効のままです。v6.6.0以降のバージョンで作成された外部キーのみが有効になります。無効な外部キーを削除し、新しい外部キーを作成することで、外部キー制約を有効にすることができます。1 SHOW CREATE TABLEを使用して、外部キーが有効かどうかを確認できます。無効な外部キーには/* FOREIGN KEY INVALID */コメントが付きます。
mysql> SHOW CREATE TABLE child\G
***************************[ 1. row ]***************************
Table | child
Create Table | CREATE TABLE `child` (
`id` int DEFAULT NULL,
`pid` int DEFAULT NULL,
KEY `idx_pid` (`pid`),
CONSTRAINT `fk_1` FOREIGN KEY (`pid`) REFERENCES `test`.`parent` (`id`) ON DELETE CASCADE /* FOREIGN KEY INVALID */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
TiDBツールとの互換性
- Dumpling外部キーと互換性があります。
MySQLとの互換性
名前を指定せずに外部キーを作成した場合、TiDBによって生成される名前はMySQLによって生成される名前とは異なります。例えば、TiDBによって生成される外部キー名はfk_1 、 fk_2 、 fk_3ですが、MySQLによって生成される外部キー名はtable_name_ibfk_1 、 table_name_ibfk_2 、 table_name_ibfk_3です。
MySQLとTiDBはどちらも「インラインREFERENCES仕様」を解析しますが、無視します。5 FOREIGN KEY定義のうち、 REFERENCES仕様のみがチェックされ、適用されます。次の例では、 REFERENCES句を使用して外部キー制約を作成します。
CREATE TABLE parent (
id INT KEY
);
CREATE TABLE child (
id INT,
pid INT REFERENCES parent(id)
);
SHOW CREATE TABLE child;
出力は、 childテーブルに外部キーが含まれていないことを示しています。
+-------+-------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------+
| child | CREATE TABLE `child` ( |
| | `id` int DEFAULT NULL, |
| | `pid` int DEFAULT NULL |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------------------------------------------------------+