外部キー制約

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	親テーブルのキーが一意であれば書き込みを許可します	外部キー値が親キーに存在する場合、または一部（または全部）が空の場合にのみ書き込みを許可します。
  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;