外部キー制約

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データベースは、非一意インデックス上に外部キー制約を作成することをサポートしています。

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

  説明

  OceanBaseデータベースでは、外部キーと参照キーの型は以下のものが可能です：

  • 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 句を定義しないでください。外部キー関係にある2つのテーブルの両方に FOREIGN KEY 句を定義し、一方を親テーブル、もう一方をサブテーブルとした場合、カスケード操作を成功させるためには、もう一方の FOREIGN KEY 句に対しても ON UPDATE CASCADE または ON DELETE CASCADE 句を定義する必要があります。どちらか一方の FOREIGN KEY 句にのみ ON UPDATE CASCADE または ON DELETE CASCADE 句が定義されている場合、カスケード操作は失敗し、エラーが表示されます。

• 外部キーが自己参照しており、その行が自身を参照している場合、かつ 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つは顧客テーブルの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)
  )