外部キー制約

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)
  )