完全性制約の使用

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

制約検査のタイミング

OceanBaseデータベースがいつ制約検査（Checking of Constraint）を実行するかを理解することは、さまざまな制約が存在する場合に許可される操作の種類を明確にするのに役立ちます。本記事では、具体的な例を通じて制約検査のタイミングを説明します。

以下の図に示すように、emp テーブルを定義します。emp テーブルに自己参照制約（Self-Referential Constraint）を定義し、mgr 列の値は empno 列の値に依存します。例を簡略化するため、以下では emp テーブルの empno (employee_id) 列と mgr (manager_id) 列のみについて説明します。

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

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

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

• 複数行の INSERT ステートメントを実行する場合、例えば SELECT ステートメントと組み合わせた INSERT ステートメントを使用すると、相互参照関係にある複数行のデータを挿入します。例えば、1行目の 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