完全性制約の使用

このセクションでは、完全性制約のチェックタイミングと制約オプションについて説明します。

制約のチェックタイミング

OceanBaseデータベースがいつ制約チェック（Checking of Constraint）を実行するかは、さまざまな制約が存在する場合に許可される操作タイプを明確にするのに役立ちます。この記事では、具体的な例を用いて制約チェックのタイミングを紹介します。

以下の図のように、emp テーブルを定義します。emp テーブルに自己参照制約（Self-Referential Constraint）を定義し、mgr 列の値は empno 列の値に依存します。例を簡略化するため、以下の内容は emp テーブルの empno (employee_id) および mgr (manager_id) 列にのみ関連します。

次に、emp テーブルに最初のデータを挿入します。この時点でテーブル内にデータがないため、mgr 列は empno 列の既存の値を参照できません。以下のシナリオに基づいてデータ挿入を実行できます：

• mgr 列に NOT NULL 制約が定義されていない場合、最初の行の mgr 列にNULL値を入力できます。外部キー制約はNULL値を許容するため、この行はテーブルに正常に挿入されます。

• 最初の行の empno 列と mgr 列に同じ値を入力することもできます。この場合、制約チェック（Constraint Checking）はステートメントの実行後に行われます。最初の行の親キー（Parent Key）と外部キー（Foreign Key）に同じ値を挿入する場合、まずステートメント（データ行の挿入）を実行し、その後、この行のデータの mgr 列の値がテーブル内のどの empno 列の値と一致するかをチェックする必要があります。

• 複数行の INSERT ステートメントを実行する場合、例えば SELECT ステートメントと組み合わせた INSERT ステートメントでは、相互参照関係にある複数行のデータが挿入されます。例えば、最初の行の empno 列の値が200、mgr 列の値が300、2行目の empno 列の値が300、mgr 列の値が200となります。

​この場合も、データベースは制約チェックをステートメントの実行終了まで遅延させます。すべてのデータ行が先に挿入され、その後、各行ごとに制約違反がないかチェックされます。

​上記の自己参照制約を使ってもう一つ例を挙げます。会社が買収され、すべての従業員番号を現在の値に5000を加算して更新する必要があるとします。これは新しい会社の従業員番号と一致させるためです。マネージャー番号も従業員番号であるため、この値も5000加算する必要があります。下の図は更新前の emp テーブルで、empno 列と mgr 列が含まれています。empno 列には3つの値：210、211、212があります。mgr 列には2つの値：210、211があります。

emp テーブルに以下のSQLを実行します：

UPDATE EMP
SET empno = empno + 5000,
mgr = mgr + 5000;

emp テーブルに定義された制約では、各 mgr 値が empno 値と一致する必要がありますが、このステートメントは実行可能です。なぜなら、制約チェックはステートメントの実行後に行われるからです。下の図は、SQLステートメントのすべての操作が終了した後に制約チェックが行われることを示しています。

まず、各従業員番号に5000を加算し、次に各マネージャー番号に5000を加算します。最初のステップでは、empno 列の値210が5210に更新されます。2番目のステップでは、empno 列の値211が5211に更新され、mgr 列の値210が5210に更新されます。3番目のステップでは、empno 列の値212が5212に更新され、mgr 列の値211が5211に更新されます。最後に制約チェックが実行されます。

上記の例は、INSERT および UPDATE ステートメントの制約チェックメカニズムを示しています。実際、UPDATE、INSERT、DELETE などの各種DMLステートメントの制約チェックメカニズムは同じです。

制約オプション

OceanBaseデータベースのOracleモードでは、外部キー制約、CHECK 制約、NOT NULL 制約はすべて ENABLE/DISABLE および VALIDATE/NOVALIDATE の2つのオプションをサポートしています。ENABLE/DISABLE は、DMLによる挿入または更新後のテーブル内の新規データに対して制約条件を満たしているかどうかのチェックを行うかどうかを決定します。VALIDATE/NOVALIDATE は、テーブル内の既存データがすべて制約条件を満たしているかどうかを示します。

制約オプションのデフォルト値

制約を作成する際、ENABLE/DISABLE オプションのデフォルトは ENABLE です。

ENABLE/DISABLE オプションが ENABLE の場合、デフォルトの VALIDATE/NOVALIDATE オプションは VALIDATE です。

ENABLE/DISABLE オプションが DISABLE の場合、デフォルトの VALIDATE/NOVALIDATE オプションは NOVALIDATE です。

制約オプションの組み合わせによる効果

• ENABLE VALIDATE は、新規データの有効性をチェックするとともに、テーブル内の既存データの有効性もチェックします。既存データが制約を満たさない場合、制約の状態を ENABLE VALIDATE に変更したり、ENABLE VALIDATE 状態で制約を作成したりすることは許可されません。

• ENABLE NOVALIDATE は、既存データの有効性をチェックせず、テーブル内の新規データの有効性のみをチェックします。

• DISABLE VALIDATE は、テーブル全体でDML操作を実行できなくします。

• DISABLE NOVALIDATE は、制約を無効な制約に変更することを指し、既存データの有効性もテーブル内の新規データの有効性もチェックしません。

制約オプションの例

obclient> CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected

obclient> INSERT INTO t1 VALUES(0, 1);
Query OK, 1 row affected 

obclient> ALTER TABLE t1 ADD CONSTRAINT cst CHECK(c1 = c2) ENABLE VALIDATE;
OBE-02293: cannot validate (TEST.CST) - check constraint violated

obclient> ALTER TABLE t1 ADD CONSTRAINT cst CHECK(c1 = c2) DISABLE VALIDATE;
OBE-02293: cannot validate (TEST.CST) - check constraint violated

obclient> ALTER TABLE t1 ADD CONSTRAINT cst CHECK(c1 = c2) ENABLE NOVALIDATE;
Query OK, 0 rows affected

obclient> INSERT INTO t1 VALUES(0, 1);
OBE-02290: check constraint violated

obclient> INSERT INTO t1 VALUES(1, 1);
Query OK, 1 row affected

obclient> ALTER TABLE t1 MODIFY CONSTRAINT cst DISABLE NOVALIDATE;
Query OK, 0 rows affected

obclient> INSERT INTO t1 VALUES(0, 1);
Query OK, 1 row affected

obclient> DELETE FROM t1 WHERE c1 != c2;
Query OK, 2 rows affected

obclient> ALTER TABLE t1 MODIFY CONSTRAINT cst DISABLE VALIDATE;
Query OK, 0 rows affected

obclient> INSERT INTO t1 VALUES(1, 1);
OBE-25128: No insert/update/delete on table with constraint (TEST.CST) disabled and validated

obclient> ALTER TABLE t1 MODIFY CONSTRAINT cst ENABLE VALIDATE;
Query OK, 0 rows affected 

obclient> INSERT INTO t1 VALUES(0, 1);
OBE-02290: check constraint violated

obclient> INSERT INTO t1 VALUES(1, 1);
Query OK, 1 row affected