外部キー制約

OceanBaseデータベースの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 | NO ACTION | SET DEFAULT

​外部キー制約に関連する特性は以下のとおりです：

• 身元識別

• 条件と制限

• 参照動作

身元識別子

外部キー制約の命名は、以下のルールによって制御されます：

• CONSTRAINT symbolが定義されている場合は、その値を使用します。

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

• この値（定義されている場合）はデータベース内で一意でなければなりません。重複する値は次のエラーを引き起こします：ERROR 1005 (HY000): Can't create table 'test.fk1' (errno: 121)。

FOREIGN KEY ... REFERENCES句のテーブルと列識別子は、バッククォート（`）で引用できます。

条件と制限

外部キー制約は、以下の条件と制限に従います：

• 外部キー制約を作成するには、REFERENCES親テーブルへの権限が必要です。親テーブル上で外部キーが参照する列には、主キーまたはインデックスが存在する必要があります。

  説明

  OceanBaseデータベースV4.3.5では、V4.3.5 BP1以降のバージョンから、非一意インデックス上に外部キー制約を作成できるようになりました。

• 外部キーと参照キーに対応する列は、類似したデータ型を持たなければなりません。INTEGERとDECIMALなどの固定小数点数型では、サイズと符号が同じである必要があります。文字列型の長さは同じである必要はありません。非バイナリ（文字）文字列列の場合、文字セットと照合順序は同じでなければなりません。

  説明

  OceanBaseデータベースV4.3.5では、V4.3.5 BP1以降のバージョンから、外部キーと参照キーの型は次のようになります：

  • UNSIGNED FLOATとFLOAT。
  • FLOATとUNSIGNED FLOAT
  • UNSIGNED DOUBLEとDOUBLE。
  • DOUBLEとUNSIGNED DOUBLE。

• OceanBaseデータベースのMySQLモードでは、参照キー（親キー）にはデフォルトでインデックスが作成されますが、外部キーにはデフォルトでインデックスが作成されません。

• 外部キー列のインデックスプレフィックスはサポートされていません。そのため、BLOBおよびTEXT列は外部キーに含めることができません。これらの列のインデックスには常にプレフィックス長が含まれる必要があるためです。

• 外部キー制約は、仮想生成された列を参照することはできません。

引用動作

UPDATEまたはDELETE操作が、サブテーブル内にマッチする行を持つ親テーブルのキー値に影響を与える場合、結果はFOREIGN KEY句のON UPDATEおよびON DELETE句で指定された参照動作によって決まります。参照動作には以下が含まれます：

• CASCADE：親テーブルから行を削除または更新し、サブテーブル内のマッチする行を自動的に削除または更新します。

  ON DELETE CASCADEとON UPDATE CASCADEがサポートされています。2つのテーブル間で、親テーブルまたはサブテーブルの同一列に対して複数のON UPDATE CASCADE句を定義しないでください。外部キー関係の両方のテーブルにFOREIGN KEY句が定義されており、両方のテーブルが親テーブルとサブテーブルになっている場合、カスケード操作を正常に実行するには、もう一方のFOREIGN KEY句にON UPDATE CASCADEまたはON DELETE CASCADE句を定義する必要があります。ON UPDATE CASCADEまたはON DELETE CASCADE句がFOREIGN KEY句のいずれか一方にのみ定義されている場合、カスケード操作は失敗し、エラーが表示されます。

• 外部キーの自己参照の場合、特定の行が自身を参照しており、かつdelete cascade動作である場合、OceanBaseデータベースMySQLモードはこの行を正常に削除できます。

• 外部キーの自己参照について、特定の行の親キーが参照されている場合、OceanBaseデータベースMySQLモードはカスケード更新を許可します。

  注意

  OceanBaseデータベースMySQLモードでは、外部キーのカスケード操作がトリガーを有効にします。

• 外部キーに環状の参照が存在する場合、または実際の更新時にテーブルの重複更新が発生した場合、システムはエラー（エラーコード1451）をトリガーします。具体的な説明と例は以下のとおりです：

  1. 自己参照外部キーのカスケード更新制限：テーブルに自己参照外部キーが存在する場合、更新操作によりサブ列が親列自体のカスケード更新を試みると、エラーがトリガーされます。例：

     //テーブルの作成
     DROP TABLE IF EXISTS t1;
     CREATE TABLE t1(c1 INT UNIQUE, c2 INT, FOREIGN KEY (c2) REFERENCES t1(c1) ON UPDATE CASCADE);
     //テストデータの挿入
     INSERT INTO t1 VALUES(1, NULL), (2, 1);
     

     以下の更新操作を実行します：

     UPDATE t1 SET t1.c1 = 10 WHERE t1.c1 = 1;
     

     実行結果は次のとおりです：

     ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
     

     原因分析：

     • テーブルt1の外部キーc2は、自身のc1列を参照しています。
     • c1の値を更新するとc2のカスケード更新がトリガーされますが、c2は自己参照外部キーであるため、システムは潜在的なループ更新を検出し、エラーを報告します。

  2. 複数テーブルの環状参照によるカスケード更新制限：複数のテーブル間に環状参照が存在する場合、カスケード更新が閉ループを形成すると（つまり、更新パスが「更新中」のテーブルに戻る）、システムはエラーをトリガーします。例：

     //テーブルの作成
     DROP TABLE IF EXISTS t1, t2, t3;
     CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT);
     CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT UNIQUE);
     CREATE TABLE t3(c1 INT PRIMARY KEY, c2 INT UNIQUE);
     //外部キー制約の作成
     ALTER TABLE t1 ADD FOREIGN KEY(c2) REFERENCES t3(c2) ON UPDATE CASCADE;
     ALTER TABLE t2 ADD FOREIGN KEY(c2) REFERENCES t1(c1) ON UPDATE CASCADE;
     ALTER TABLE t3 ADD FOREIGN KEY(c2) REFERENCES t2(c2) ON UPDATE CASCADE;
     //テストデータの挿入
     INSERT INTO t1 VALUES(1, NULL);
     INSERT INTO t2 VALUES(1, 1);
     INSERT INTO t3 VALUES(1, 1);
     INSERT INTO t1 VALUES(2, 1);
     

     以下の更新操作を実行します：

     UPDATE t1 SET t1.c1 = 10 WHERE t1.c1 = 1;
     

     実行結果は次のとおりです：

     ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
     

     原因分析：

     • テーブルt1、t2、t3は環状参照を形成しています：t1 → t2 → t3 → t1。
     • t1の値を更新すると、順番にt2、t3、t1へのカスケード更新がトリガーされ、最終的にt1に戻るため、閉ループが形成され、エラーが報告されます。

  3. 複数テーブルの更新に関連するカスケード更新制限：複数のテーブルに関連する更新操作において、UPDATE文がUPDATE文に含まれるテーブルのカスケード更新を直接または間接的にトリガーする場合、エラーがトリガーされます。例：

     //テーブルの作成
     DROP TABLE IF EXISTS t1, t2, t3;
     CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT);
     CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT UNIQUE, c3 INT);
     CREATE TABLE t3(c1 INT PRIMARY KEY, c2 INT UNIQUE, c3 INT);
     //外部キー制約の作成
     ALTER TABLE t2 ADD FOREIGN KEY(c2) REFERENCES t1(c1) ON UPDATE CASCADE;
     ALTER TABLE t3 ADD FOREIGN KEY(c2) REFERENCES t2(c2) ON UPDATE CASCADE;
     //テストデータの挿入
     INSERT INTO t1 VALUES(1, 5);
     INSERT INTO t2 VALUES(1, 1, 5);
     INSERT INTO t3 VALUES(1, 1, 5);
     

     以下の更新操作を実行します：

     UPDATE t1, t2, t3 SET t1.c1 = 10, t2.c1 = 20 WHERE t1.c2 = t2.c3 AND t2.c3 = t3.c3;
     

     実行結果は次のとおりです：

     ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
     

     原因分析：

     t1の更新により外部キーのカスケード更新t2、t3がトリガーされますが、t2はUPDATEに含まれており、t2を更新する際に暗黙のループt1->t2->t3->t2が存在することが検出されたため、エラーが報告されました。

• RESTRICT：親テーブルの削除または更新操作を拒否します。

  RESTRICT(またはNO ACTION)を指定することは、ON DELETEまたはON UPDATE句を省略することと同じです。

• NO ACTION: 標準SQLのキーワードです。

  OceanBaseデータベースMySQLモードでは、これはRESTRICTに相当します。参照されるテーブルに関連する外部キー値が存在する場合、OceanBaseデータベースは親テーブルの削除または更新操作を拒否します。一部のデータベースシステムでは遅延チェックがあり、NO ACTIONは遅延チェックです。MySQLでは、外部キー制約が即座にチェックされるため、NO ACTIONとRESTRICTは同じ意味を持ちます。

指定されていないON DELETEまたはON UPDATEのデフォルトの動作は常にNO ACTIONです。デフォルトでは、明示的に指定されたON DELETE NO ACTIONまたはON UPDATE NO ACTION句はSHOW CREATE TABLEの出力には表示されません。

外部キー制約の一般的な操作

外部キー制約の追加

次の ALTER TABLE 構文を使用して、既存のテーブルに外部キー制約を追加します：

​ALTER TABLE table_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 table_name DROP FOREIGN KEY fk_symbol;

​制約を作成する際に FOREIGN KEY 句で定義された CONSTRAINT 名前がある場合、その名前を使用して外部キー制約を削除できます。そうでない場合、内部で制約名が生成され、その値を使用しなければなりません。外部キー制約の名前を確認するには、SHOW CREATE TABLE を使用します。

外部キー検査

外部キー検査は、デフォルトで有効になっている foreign_key_checks 変数によって制御されます。通常、参照整合性を強制するために、通常の運用時にこの変数を有効にします。

次の状況では、foreign_key_checks を無効にすることでデータベースにプラスの影響があります：

• 外部キー制約によって参照されているテーブルを削除します。foreign_key_checks を無効にした後でないと、参照されているテーブルを削除できません。テーブルを削除すると、テーブル上で定義された制約も削除されます。

• 外部キー関係に必要な順序とは異なる順序でテーブルを再ロードします。

• データインポート操作時に外部キー検査をオフにすることで、データインポートを高速化できます。

• 外部キー関係を持つテーブルに対して ALTER TABLE 操作を実行します。​

同様に、foreign_key_checks を無効にすることで、いくつかのマイナスの影響も生じます：

• データベース外部のテーブルによって参照されている、外部キーを含むテーブルをデータベースから削除できるようになります。

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

foreign_key_checks を有効にしても、テーブルデータのスキャンはトリガーされません。これは、foreign_key_checks が再び有効になった場合、foreign_key_checks を無効にした際にテーブルに追加された行の一貫性がチェックされないことを意味します。

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

外部キーの定義を確認するには、SHOW CREATE TABLE を使用します。例：

​obclient> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
​Table: child
Create Table: CREATE TABLE `child` (
​`id` int(11) DEFAULT NULL,
​`parent_id` int(11) DEFAULT NULL,
​CONSTRAINT `child_OBFK_1633952161788605` FOREIGN KEY (`parent_id`) REFERENCES `test`.`parent`(`id`) ON UPDATE RESTRICT ON DELETE CASCADE ,
​KEY `par_ind` (`parent_id`) BLOCK_SIZE 16384 GLOBAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0

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

obclient> 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_OBFK_1633952161788605 |
+--------------+------------+-------------+-----------------------------+

外部キー制約の例

• 単一列外部キーを使用して、親テーブルと子テーブルを関連付けます。

  CREATE TABLE parent (
   id INT NOT NULL,
   PRIMARY KEY (id)
  )
  ​
  CREATE TABLE child (
   id INT,
   parent_id INT,
   INDEX par_ind(parent_id),
   FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE CASCADE
  )
  

• product_order テーブルには、他の2つのテーブルからの外部キーがあります。1つの外部キーは製品テーブルの2列インデックスを参照し、もう1つは顧客テーブルの単一列インデックスを参照します。

  CREATE TABLE product (
   category INT NOT NULL,
   id INT NOT NULL,
   price DECIMAL,
   PRIMARY KEY (category, id)
  )
  ​
  CREATE TABLE customer (
   id INT NOT NULL,
   PRIMARY KEY (id)
  )
  ​
  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 DELETE RESTRICT ON UPDATE CASCADE,
   FOREIGN KEY (customer_id) REFERENCES customer (id)
  )