完全性制約の使用

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

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

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 の2つの列が含まれています。empno 列には210、211、212の3つの値があります。mgr 列には210と211の2つの値があります。

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