外部キー制約

2つのテーブルに1つ以上の共通列が存在する場合、OceanBaseデータベースのOracleモードでは外部キー制約（参照整合性制約とも呼ばれる）を使用して、2つのテーブル間の関係を強制的に実行できます。

外部キー制約の特性

外部キー制約では、制約を定義した列の各値が、別の指定されたテーブルの特定の列の値と一致する必要があります。構文は以下のとおりです：

REFERENCES [ schema. ] object [ (column_name [, column_name...]) ] [ON DELETE { CASCADE | SET NULL } ]

参照整合性制約に関連する用語は、次の表のとおりです。

例：外部キー制約を含むテーブルを作成します。

obclient> CREATE TABLE supplier_groups(
     group_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
     group_name VARCHAR2(127) NOT NULL,
​     PRIMARY KEY (group_id)
      );
Query OK, 0 rows affected

obclient> CREATE TABLE suppliers (
     supplier_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
     supplier_name VARCHAR2(127) NOT NULL,
     group_id NUMBER NOT NULL,
     PRIMARY KEY(supplier_id),
     CONSTRAINT fk_name FOREIGN KEY(group_id) REFERENCES supplier_groups(group_id)
     );
Query OK, 0 rows affected

外部キー制約の主な特性は以下のとおりです：

• 自己参照整合性制約

  自己参照整合性制約は、同一テーブル内の親キーを参照する外部キーです。例：

  obclient> CREATE TABLE employee (
  employee_id INT PRIMARY KEY,
  employee_name VARCHAR(30),
  salary VARCHAR(30),
  manager_id INT,
  CONSTRAINT sr_fk_emp_man FOREIGN KEY (manager_id) REFERENCES employee(employee_id)
  );
  

• NULL値と外部キー

  関係モデルでは、外部キーの値は参照先の主キーまたは一意キーの値と一致するか、NULL値を許容します。複合外部キーのいずれかの列がNULLの場合、キーのNULLでない部分は親キーの対応する部分と一致させる必要はありません。

• 親キーの変更と外部キー

  外部キーと親キーの関係は、親キーの削除に影響します。親キーを変更する際、参照整合性制約により、子テーブル内の影響を受ける行に対してDELETE NO ACTIONまたはDELETE CASCADE操作を実行することを指定できます。

  以下の表は、親テーブルのキー値と子テーブルの外部キー値に対する異なる参照操作で許可されるDMLステートメントを概説したものです。

  DMLステートメント	親テーブルに対する操作	子テーブルに対する操作
  INSERT	親テーブルのキーが一意であれば書き込みを許可します	外部キー値が親テーブルのキーに存在する場合、または一部（または全部）がNULLの場合にのみ書き込みを許可します。
  DELETE NO ACTION	子テーブルにマッチするレコードがない場合、親テーブルの対応するキーの削除操作を許可します。	制限なし
  DELETE CASCADE	制限なし	制限なし

• インデックスと外部キー

  一般的に、外部キー列はインデックス列であるべきです。唯一の例外は、一致する一意キーまたは主キーが更新または削除されていない場合です。子テーブル内の外部キーにインデックスを付ける利点は以下のとおりです：

  • 子テーブルでのテーブル全体のロックを防ぎます。データベースはインデックス上の行ロックのみを取得する必要があります。

  • 子テーブルのテーブル全体スキャンが不要です。​

  注意

  OceanBaseデータベースのOracleモードでは、外部キーは現在以下の動作をサポートしていません：

  • SET NULL動作はサポートされていません。
  • ステートメントレベルの検出はサポートされていません。
  • 遅延制約チェックはサポートされていません。

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

• テーブルに外部キー制約を追加します。

  ALTER TABLE child ADD CONSTRAINT fk_name_1
  FOREIGN KEY (col1) REFERENCES parent (col1);
  

• 外部キー制約を削除します。

  ALTER TABLE child DROP CONSTRAINT fk_name_1;
  

• 外部キー制約を無効にします。

  ALTER TABLE child DISABLE CONSTRAINT fk_name_1;
  

• 外部キー制約を有効にします。

  ALTER TABLE child ENABLE CONSTRAINT fk_name_1;