PolarDB:外部キー

外部キーの作成

外部キー機能を有効にする

次のパラメーターを使用して、外部キー機能を有効または無効にできます。

SET [グローバル] enable_foreign_key = [true | false]

構文

[CONSTRAINT [シンボル]] 外国キー
    [index_name] (col_name, ...)
    参考文献tbl_name (col_name,...)
    [DELETE reference_option]
    [更新参照_オプション]

reference_option:
    RESTRICT | CASCADE | SET NULL | アクションなし | SET DEFAULT 

名前付け

外部キーは、次の命名規則に準拠している必要があります。

• CONSTRAINTステートメントで名前が指定されている場合は、その名前が使用されます。

• CONSTRAINTステートメントで名前を指定しない場合は、名前が自動的に生成されて使用されます。

• index_nameパラメーターは、外部キーとともに作成されるインデックスの名前としてのみ使用され、外部キー名ではありません。

• 外部キー名は、現在のテーブルで一意である必要があります。

サポートされているデータベースタイプ

外部キーは、AUTOモードとDRDSモードの両方でデータベース用に作成できます。 外部キーは、AUTOデータベースとDRDSデータベースの間で作成することもできます。

制限

• InnoDBを使用するデータベースでのみ外部キーを作成できます。

• 外部キーの列は、親テーブルの列と同じデータ型である必要があります。 それらのサイズ、精度、長さ、文字セット、および照合順序は同じでなければなりません。

• 外部キーの列は、親テーブルの列に対応している必要があります。 外部キーと親テーブルに同じ数の列を作成する必要があります。

• 外部キー名、インデックス名、テーブル名、および列名の長さは最大64文字です。

• 外部キーの列は、親テーブルの列と同じインデックスを使用する必要があります。 インデックスの列は、外部キーの列と同じ順序である必要があります。 これにより、テーブル全体をスキャンする代わりに、外部キー制約のチェックにインデックスを使用できるようになります。

  子テーブルに外部キーインデックスがない場合、インデックスが自動的に作成されます。 インデックス名はカスタマイズすることも、外部キー名と同じにすることもできます。

• プレフィックスインデックスはサポートされていません。 したがって、BLOBおよびTEXTデータ型の列に外部キーを作成することはできません。

• 参照メソッドがSET NULLの外部キーの参照列はNOT NULL (主キー) にすることはできません。

• 参照メソッドがSET DEFAULTである外部キーは作成できません。

MySQLとは異なる制限

生成された列 (stored、virtual、およびlogical) に外部キーを作成することはできません。

参照アクション

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

外部キー制約の例 (AUTOモードの場合)

このトピックのすべての例は、AUTOモードの外部キー機能を示しています。

次の例では、親テーブルと子テーブルの1つの列に外部キーを作成します。

CREATE TABLEの親 (
    id INT NOT NULL,
    主要なキー (id)
);

テーブルの子を作成する (
    id INT,
    parent_id INT、
    INDEX par_ind (parent_id) 、
    FOREIGN KEY (parent_id) DELETE CASCADEの親 (id) の参照
); 

次の例では、product_orderテーブルに2つの外部キーを作成します。 最初の外部キーは、製品テーブルの2列インデックスを参照します。 2番目の外部キーは、顧客テーブルの単一列インデックスを参照します。

テーブルプロダクトを作成する (
    カテゴリINT NOT NULL、id INT NOT NULL、
    デシマル価格、
    主要なキー (category, id)
);

テーブルの顧客を作成する (
    id INT NOT NULL,
    主要なキー (id)
);

CREATE TABLE product_order (
    INT NOT NULLなしAUTO_INCREMENT、
    product_category INT NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,

    主要なキー (いいえ) 、
    インデックス (product_category, product_id),
    インデックス (customer_id) 、

    FOREIGN KEY (product_category, product_id)
      REFERENCES製品 (カテゴリ、id)
      削除制限のキャスケードの更新について、

    外国キー (customer_id)
      REFERENCES customer(id)
); 

外部キー制約の作成

次のALTER tableステートメントを使用して、既存のテーブルに外部キー制約を作成できます。

ALTER TABLE tbl_name
    追加 [CONSTRAINT [シンボル]] 外国キー
    [index_name] (col_name, ...)
    参考文献tbl_name (col_name,...)
    [DELETE reference_option]
    [更新参照_オプション] 

外部キーは、同じテーブルのインデックスを参照できます。 ALTER TABLEステートメントを実行してテーブルに外部キー制約を作成する場合は、まず外部キーによって参照される列にインデックスを作成する必要があります。

外部キー制約の削除

次のALTER tableステートメントを使用して、既存のテーブルから外部キー制約を削除できます。

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

外部キー制約を作成するときに外部キー名を指定した場合、指定した名前を使用して外部キー制約を削除できます。 それ以外の場合は、自動的に生成された名前を使用してのみ外部キー制約を削除できます。 SHOW CREATE TABLEステートメントを実行すると、外部キー名を表示できます。

SHOW CREATE TABLE child\G
*************************** 1。 行 ***************************
       テーブル: 子供
テーブルの作成: Create Table 'child' (
'id' int(11) DEFAULT NULL、'parent_id' int (11) DEFAULT NULL、CONSTRAINT 'childr_ibfk_1 'FOREIGN KEY ('parent_id') REFERENCES 'parent' ('id') ON DELETE CASCADE ON UPDATE NO ACTION、インデックス 'par_ind' ('parent_id')
) エンジン=InnoDBデフォルト料金=utf8mb4 

外部キーチェック

PolarDB-Xは外部キーチェックをサポートしています。 foreign_key_checksパラメーターを使用して、デフォルトで有効になっている外部キーチェック機能を無効にできます。 外部キーチェック機能を有効にすると、通常、外部キー参照の整合性が保証されます。

foreign_key_checksパラメーターは、グローバルレベルまたはセッションレベルで有効になります。

外部キーチェック機能を無効にすると、次のような場合に便利です。

• 外部キーによって参照される親テーブルを削除する前に、外部キーチェック機能を無効にする必要があります。

• データベースにデータをインポートすると、外部キーと異なる順序でテーブルが作成されると、エラーが発生します。 テーブルを作成する前に、外部キーチェック機能を無効にする必要があります。 さらに、外部キーチェック機能を無効にすると、データをインポートするときのデータのインポートも高速化されます。

• データベースにデータをインポートすると、子テーブルにインポートされたデータに対してエラーが報告されます。 データを子テーブルにインポートする前に、外部キーチェック機能を無効にする必要があります。

• 外部キーに関するALTER TABLEステートメントを実行する前に、外部キーチェック機能を無効にする必要があります。

外部キーチェック機能を無効にすると、次の例外を除いて外部キーチェックは無視されます。

• テーブルを再構築すると、外部キーの定義が正しくない場合でも、エラーが報告されます。

• 外部キーに必要なインデックスを削除する場合は、最初に外部キーを削除する必要があります。 外部キーに必要なインデックスを削除すると、エラーが報告されます。

• 外部キーの作成時に外部キーの条件または制限が満たされない場合でも、エラーが報告されます。

• データベース間外部キーによって参照される親テーブルが存在するデータベースを削除すると、エラーが報告されます。

外部キーチェック機能を無効にすると、次のような意味があります。

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

• 外部キーチェック機能を有効にしても、テーブル内のデータはスキャンされません。 したがって、外部キーチェック機能を再度有効にすると、外部キーチェック機能が無効になっているときにテーブルに追加されたデータの整合性がチェックされません。

ロック

INSERTまたはUPDATEステートメントが子テーブルで実行されると、外部キー制約は、対応する外部キー値が親テーブルに存在するかどうかをチェックし、トランザクションを開始し、親テーブルの行をロックします。 これは、外部キー制約を破壊し得る他の操作によって外部キー値が修正されることを防止する。 このロックは、親テーブルの外部キー値が配置されている行に対してSELECT FOR UPDATEステートメントを実行することと同じです。 データが子テーブルに同時に書き込まれるシナリオでは、参照される外部キー値のほとんどが同じである場合、深刻なロック競合が発生する可能性があります。

外部キー定義とメタデータ

外部キー定義を表示するには、SHOW CREATE TABLEまたはSHOW FULL CREATE TABLE (論理外部キーまたは物理外部キーの確認) ステートメントを実行します。

SHOW CREATE TABLE child\G
*************************** 1。 行 ***************************
       テーブル: 子供
テーブルの作成: Create Table 'child' (
'id' int(11) DEFAULT NULL、'parent_id' int (11) DEFAULT NULL、CONSTRAINT 'childr_ibfk_1 'FOREIGN KEY ('parent_id') REFERENCES 'parent' ('id') ON DELETE CASCADE ON UPDATE NO ACTION、インデックス 'par_ind' ('parent_id')
) エンジン=InnoDBデフォルト料金=utf8mb4

フル作成テーブルの子 \Gを表示
*************************** 1。 行 ***************************
       テーブル: 子供
テーブルの作成: PARTITION Table 'child '(
'id' int(11) DEFAULT NULL、'parent_id' int (11) DEFAULT NULL、'_drds_implicit_id_' bigint(20) NOT NULL AUTO_INCREMENT、PRIMARY KEY ('_drds_implicit_id_') 、CONSTRAINT 'childr_ibfk_1 'FOREIGN KEY ('parent_id') REFERENCES 'parent' ('id') ON DELETE CASCADE ON UPDATE NO ACTION /* TYPE LOGICAL * /、グローバルインデックス /* par_ind_$871c */ 'par_ind' ('parent_id')
PARTITION BY KEY('parent_id','_drds_implicit_id_')
パーティー3,
ローカルキー '_local_par_ind' ('parent_id')
) エンジン=InnoDBデフォルト料金=utf8mb4
PARTITION BY KEY('_drds_implicit_id_')
パーティー3
/* tablegroup = 'tg1121' */ 

外部キーに関する情報は、次のシステムテーブルからも取得できます。

• INFORMATION_SCHEMA.TABLE_CONSTRAINTS

• INFORMATION_SCHEMA.KEY_COLUMN_USAGE

• INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

• INFORMATION_SCHEMA.INNODB_SYS_FOREIGN

• INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE REFERENCED_TABLE_SCHEMA = 'test';
+ ------------ ---------------------
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME |
+ ------------ ---------------------
| test | product_order | customer_id | product_order_ibfk_2 |
| test | child | parent_id | child_ibfk_1 |
| test | product_order | product_category | product_order_ibfk_1 |
| test | product_order | product_id | product_order_ibfk_1 |
+ ------------ ---------------------

CONSTRAINT_TYPE='FOREIGN KEY' およびCONSTRAINT_SCHEMA = 'test'\Gから * を選択します。*************************** 1。 行 ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: テスト
   CONSTRAINT_NAME: child_ibfk_1
      TABLE_SCHEMA: テスト
        TABLE_NAME: 子
   CONSTRAINT_TYPE: 外国キー
          ENFORCED: はい

SELECT * FROM CONSTRAINT_SCHEMA = 'test'\G
*************************** 1。 行 ***************************
       CONSTRAINT_CATALOG: def
        CONSTRAINT_SCHEMA: テスト
          CONSTRAINT_NAME: child_ibfk_1
UNIQUE_CONSTRAINT_CATALOG: def
 UNIQUE_CONSTRAINT_SCHEMA: テスト
   UNIQUE_CONSTRAINT_NAME: プライマリー
             MATCH_OPTION: なし
              UPDATE_RULE: アクションなし
              DELETE_RULE: カスケード
               TABLE_NAME: 子
    REFERENCED_TABLE_NAME: 親
    
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN WHERE ID = 'test/child_ibfk_1 ';
+ ------------------- + -------------------------------------------------- +
| ID | FOR_NAME | REF_NAME | N_COLS | タイプ |
+ ------------------- + -------------------------------------------------- +
| test/child_ibfk_1 | test/child | test/parent | 1 | 33 |
+ ------------------- + -------------------------------------------------- +

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS WHERE ID = 'test/child_ibfk_1 ';
+ ------------------- ------------ --------------------------- +
| ID | FOR_COL_NAME | REF_COL_NAME | POS |
+ ------------------- ------------ --------------------------- +
| test/child_ibfk_1 | parent_id | id | 0 |
+ ------------------- ------------ ------------------------------- + 

外部キーカスケードの実行計画を表示する

EXPLAINステートメントを実行すると、外部キーのカスケードに関与する可能性のある実行計画を表示できます。 外部キーフィールドは、チェックされる外部キーを示す。

EXPLAIN DELETE FROM親WHERE id = 1;
+ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +
| ローカル実行 |
+ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +
| LogicalModify(TYPE="DELETE" 、TABLES="test.parent") |
| LogicalView(tables="parent[p3]", sql="SELECT 'id' FROM 'parent' AS 'parent' WHERE ('id' = ?) FOR UPDATE") |
| >> 外部キー: test.child.child_ibfk_1 |
| LogicalModify(TYPE="DELETE" 、TABLES="test.child") |
| 収集 (同時=true) |
| LogicalView(tables="child[p1,p2,p3]", shardCount=3, sql="SELECT 'id', 'parent_id' FROM 'child' AS 'child' WHERE ('parent_id' = ?) FOR UPDATE") |
| >>>> 外部キー: test.grand_child.grand_child_ibfk_1 |
| LogicalModify(TYPE="DELETE" 、TABLES="test.grand_child") |
| 収集 (同時=true) |
| LogicalView(tables="grand_child[p1,p2,p3]", shardCount=3, sql="SELECT 'id', 'parent_id' FROM 'grand_child' AS 'grand_child' WHERE ('parent_id' = ?) FOR UPDATE") |
| HitCache:true |
| ソース: PLAN_CACHE |
| TemplateId: 78fcce0f |
+ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + 

EXPLAIN COSTステートメントを実行して、コストを見積もり、複雑なカスケード操作によって引き起こされる高パフォーマンスのオーバーヘッドを回避することもできます。

詳細については、「EXPLAIN」をご参照ください。

EXPLAIN COST DELETE FROM parent WHERE id = 1;
+ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +
| ローカル実行 |
+ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +
| LogicalModify(TYPE="DELETE" 、TABLES="test.parent"): rowcount = 1.0、累積コスト=値=1.0005002E7、cpu = 2.0、メモリ=0.0、io = 1.0、net = 2.0 |
| LogicalView(tables="parent[p3]" 、sql="SELECT 'id' FROM 'parent' AS 'parent' WHERE ('id' = ?) FOR UPDATE"): rowcount = 1.0、累積コスト=値=5005001.0、cpu = 1.0、メモリ=0.0、io = 1.0、net = 1.0 |
| >> 外部キー: test.child.child_ibfk_1 |
| LogicalModify(TYPE="DELETE" 、TABLES="test.child"): rowcount = 1.0、累積コスト=値=1.2505003E7、cpu = 3.0、メモリ=0.0、io = 1.0、net = 2.5 |
| 収集 (同時=true): rowcount = 1.0、累積コスト=値=7505002.0、cpu = 2.0、メモリ=0.0、io = 1.0、net = 1.5 |
| LogicalView(tables="child[p1,p2,p3]", shardCount=3, sql="SELECT 'id', 'parent_id' FROM 'child 'AS 'child' WHERE ('parent_id' = ?) FOR UPDATE"): rowcount = 1.0, 累積コスト=値=7505001.0, cpu = 1.0, memory = 0.0, io = 1.0 | net 1.5
| >>>> 外部キー: test.grand_child.grand_child_ibfk_1 |
| LogicalModify(TYPE="DELETE" 、TABLES="test.grand_child"): rowcount = 1.0、累積コスト=value = 1.2505003E7、cpu = 3.0、memory = 0.0、io = 1.0、net = 2.5 |
| 収集 (同時=true): rowcount = 1.0、累積コスト=値=7505002.0、cpu = 2.0、メモリ=0.0、io = 1.0、net = 1.5 |
| LogicalView(tables="grand_child[p1,p2,p3]" 、shardCount=3、sql="SELECT 'id' 、'parent_id' FROM 'grand_child' WHERE ('parent_id' = ?) FOR UPDATE"): rowcount = 1.0、累積コスト=値=7505001.0、cpu=0.0 |=1.0、net
| HitCache:true |
| ソース: PLAN_CACHE |
| WorkloadType: TP |
| TemplateId: 78fcce0f |
+ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + 

互換性

外部キーチェックとプッシュダウン

外部キーを作成するとき、PolarDB-Xは、テーブル構造に基づいて外部キーを押下するかどうかを決定します。 プッシュダウンされた外部キーは物理外部キーと呼ばれ、プッシュダウンされていない外部キーは論理外部キーと呼ばれます。 外部キーを押すとパフォーマンスが向上します。

外部キーチェック機能を無効にした後に親テーブルを削除して再構築するか、親テーブル構造が不明のままで外部キーを含む子テーブルを最初に作成する場合は、論理外部キーがデフォルトで作成されます。

MySQLとの互換性

マッチング方法

MySQLでは、外部キー制約は、MATCH SIMPLE、MATCH FULL、MATCH PARTIALの3つのマッチング方法をサポートしています。 MATCH SIMPLEはデフォルトで使用されます。 MATCH PARTIALは後でサポートされます。 PolarDB-Xは、デフォルトのMATCH SIMPLEメソッドをサポートしています。 マッチング方法は変更できません。

内部参加参照

MySQLとPolarDB-Xはどちらも解析できますが、内部参加REFERENCESによって定義された外部キーは無視できます。 REFERENCESパラメーターは、外部キー定義の一部である場合にのみチェックおよび実行されます。 次の例では、外部キー制約が定義されている場合にのみREFERENCESを使用します。

CREATE TABLEの親 (
    id INTキー
);

テーブルの子を作成する (
    id INT,
    pid INT REFERENCES親 (id)
);

ショー作成テーブルの子; 

結果は、子テーブルに外部キーが含まれていないことを示します。

SHOW CREATE TABLE child\G
*************************** 1。 行 ***************************
       テーブル: 子供
テーブルの作成: Create Table 'child' (
'id' int(11) DEFAULT NULL、'pid' int (11) DEFAULT NULL
) エンジン=InnoDBデフォルト料金=utf8mb4