外部キー制約
v6.6.0 以降、TiDB は、関連データのテーブル間参照を可能にする外部キー機能と、データの一貫性を維持するための外部キー制約をサポートします。
外部キーは子テーブルで定義されます。構文は次のとおりです。
- 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]
外部キーは自己参照、つまり同じテーブルを参照することができます。 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(11) DEFAULT NULL,
`pid` int(11) 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(11) DEFAULT NULL,
`pid` int(11) 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 以降のバージョンで作成SHOW CREATE TABLEれた外部キーのみが有効になります。無効な外部キーを削除して新しい外部キーを作成し、外部キー制約を有効にすることができます。1 ステートメントを使用して、外部キーが有効かどうかを確認できます。無効な外部キーには/* FOREIGN KEY INVALID */コメントがあります。
mysql> SHOW CREATE TABLE child\G
***************************[ 1. row ]***************************
Table | child
Create Table | CREATE TABLE `child` (
`id` int(11) DEFAULT NULL,
`pid` int(11) 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ツールとの互換性
- TiDBBinlog外部キーをサポートしません。
- DM外部キーをサポートしていません。DM は、データを TiDB に複製するときに、下流の TiDB の
foreign_key_checks無効にします。そのため、外部キーによって発生するカスケード操作は上流から下流に複製されず、データの不整合が発生する可能性があります。 - ティCDC v6.6.0 は外部キーと互換性があります。以前のバージョンの TiCDC では、外部キーを持つテーブルを複製するときにエラーが報告される可能性があります。v6.6.0 より前のバージョンの TiCDC を使用する場合は、ダウンストリーム TiDB クラスターの
foreign_key_checks無効にすることをお勧めします。 - BR v6.6.0 は外部キーと互換性があります。以前のバージョンのBRでは、外部キーを持つテーブルを v6.6.0 以降のクラスターに復元するときにエラーが報告される可能性があります。v6.6.0 より前のBRを使用する場合は、クラスターを復元する前に、ダウンストリーム TiDB クラスターの
foreign_key_checksを無効にすることをお勧めします。 - TiDB Lightning使用する場合、ターゲット テーブルが外部キーを使用している場合は、データをインポートする前に、ダウンストリーム TiDB クラスターの
foreign_key_checks無効にすることをお勧めします。v6.6.0 より前のバージョンでは、このシステム変数を無効にしても効果がなく、ダウンストリーム データベース ユーザーにREFERENCES権限を付与するか、ダウンストリーム データベースにターゲット テーブルを事前に手動で作成して、スムーズなデータ インポートを確保する必要があります。
- Dumplingは外部キーと互換性があります。
- 同期差分インスペクターを使用して上流データベースと下流データベース間でデータを比較する場合、データベースのバージョンが異なり、 下流の TiDB に無効な外部キーがあるがあると、sync-diff-inspector によってテーブル スキーマの不整合エラーが報告されることがあります。これは、TiDB v6.6.0 で無効な外部キーに対して
/* FOREIGN KEY INVALID */コメントが追加されたためです。
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(11) DEFAULT NULL, |
| | `pid` int(11) DEFAULT NULL |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------------------------------------------------------+