CREATE ASSERTION

目的

アサーションは、制約のセマンティクスを持つブール式です。トランザクションでデータが変更され、これらの変更がコミットされる間、データベースはこれらのブール式の真偽を確認する必要があります。

前提条件

アサーションはスキーマ・レベルのオブジェクトです。ブール式が他のスキーマの表を参照する場合は、これらの表にアクセスするために必要なオブジェクト権限が必要です。

外部キー制約が別のスキーマの親表を参照する場合は、親表に対するREFERENCESオブジェクト権限が必要です。

アサーションで別のスキーマ内の表を参照するには、それらの表に対するASSERTION REFERENCESオブジェクト権限が必要です。また、他のスキーマの表の前にスキーマ名を付ける必要があります(SYNONYMsはアサーションではサポートされていません)。

自分のスキーマ内にアサーションを作成するには、CREATE ASSERTIONシステム権限が必要です。CREATE ASSERTION権限では、ALTER ASSERTIONおよびDROP ASSERTIONを使用して、同じスキーマ内のアサーションを変更および削除できます。

任意のスキーマまたは指定したスキーマにアサーションを作成するには、CREATE ANY ASSERTION [ON SCHEMA...]システム権限が必要です。

制約およびアサーションは同じネームスペース内に存在することに注意してください。アサーションには、同じスキーマ内の制約によってすでに使用されている名前を指定できず、その逆も同様です。

構文


create_assertion.epsの説明が続きます
図create_assertion.epsの説明

existential_expression::=


existential_expression.epsの説明が続きます
図existential_expression.epsの説明

universal_expression::=


universal_expression.epsの説明が続きます
図universal_expression.epsの説明

assertions_constraint_state::=


assertions_constraint_state.epsの説明が続きます
図assertions_constraint_state.epsの説明

セマンティクス

IF NOT EXISTS

IF NOT EXISTSを指定すると、すでに存在するアサーションを作成した場合、ORA-00955: 「すでに使用されているオブジェクト名です。」エラーがマスクされます。

existential_expression

[NOT] EXISTSの副問合せは、ベース・スキーマ表にアクセスする必要があり、複数の表の等価結合にできます。副問合せには、論理ANDまたはORの通常の列フィルタを含めることができます。

これらの列フィルタ述語は、次のようになります

  • <、>、<>(!=)、BETWEENを使用して、列またはリテラル、あるいはその両方を比較します。

  • +、-、*、/、SUBSTR、UPPERなどの列に対するすべての操作を含めます。

  • T1.c1 + T2.C2 = T3.C3の形式です。

副問合せには、ネストされた[NOT] EXISTS副問合せ述語を混在させることもできます。この述語は、最大3レベルの深さまで使用できます。これらのネストされた副問合せは、問合せブロックにさらに相関させることも、または相関させないこともできます。または、[NOT] EXISTS構文のかわりに[NOT] IN構文を使用することもできます。

universal_expression

アサーションでは、新しいALL...SATISFY句が導入されています。ALL...SATISFYは、アサーションを指定する際の否定の数を減らします。次の例は、新しい構文を示します。

existential_expressionを使用したアサーションの作成 

CREATE ASSERTION no_empty_departments CHECK  
(NOT EXISTS  
  (SELECT 'an empty department' 
     FROM dept d 
     WHERE NOT EXISTS 
                   (SELECT 'an employee in the department' 
                      FROM emp e 
                      WHERE e.deptno = d.deptno)));

前述のアサーションは、すべての部門に少なくとも1人の従業員が含まれていることが必要です。この仕様では、2つの否定が使用されます。つまり、2つの(ネストされた) NOT EXISTS述語があります。

否定を削除する同じアサーションをuniversal_expressionで指定できます。

universal_expressionを使用したアサーションの作成 

CREATE ASSERTION no_empty_departments CHECK
(ALL (SELECT d.deptno 
       FROM dept d) da 
 SATISFY 
  (EXISTS 
    (SELECT '' 
       FROM emp e 
       WHERE e.deptno = da.deptno)));

  • ALLは、表subquery (ALL query_block)を評価します。

  • 副問合せの各行について、テスト条件はSATISFYブール式によって評価されます。
  • すべての行のテスト条件がTrueの場合、ALL...SATISFY述語はTrueです。これには特殊なケースが含まれます。行がない場合、ALL...SATISFYはTrueです。
  • 少なくとも1つの行のテスト条件がFalseの場合、ALL...SATISFY述語はFalseです。
  • それ以外の場合、ALL...SATISFY述語は不明であり、制約の場合と同様にTrueとみなされます。

[NOT] DEFERRABLE [ INTIALLY ] IMMEDIATE | DEFERRED

アサーションは、DEFERRABLEまたはNOT DEFERRABLE、INITIALLY IMMEDIATEまたはINITIALLY DEFERREDなど、様々な状態があります。動作は制約と同じです。

DEFERRABLE句

DEFERRABLEおよびNOT DEFERRABLEパラメータは、後続のトランザクションで、SET CONSTRAINT(S)文を使用して、トランザクションが終了するまでアサーションのチェックを遅延できるようにするかどうかを指定します。この句を指定しない場合、NOT DEFERRABLEがデフォルトになります。

  • NOT DEFERRABLEを指定すると、後続のトランザクションで、SET CONSTRAINT[S]句を使用して、トランザクションがコミットされるまでこのアサーションのチェックを遅延させることができません。NOT DEFERRABLE SQLアサーションのチェックは、そのトランザクションの終了まで遅延させることはできません。

  • DEFERRABLEを指定すると、後続のトランザクションで、SET CONSTRAINT[S]句を使用して、COMMIT文が発行されるまでこのアサーションのチェックを遅延させることができます。アサーションのチェックに失敗した場合、エラーが戻され、トランザクションはコミットされません。この設定によって、制約に違反する可能性のある変更をデータベースに行っている場合に、すべての変更が完了するまで、制約を一時的に使用禁止にすることが可能になります。

アサーションの遅延可能状態は変更できません。これらのパラメータのいずれを指定するか、どちらも指定せずにNOT DEFERRABLEアサーションを暗黙的に有効にするかどうかに関係なく、この句をALTER ASSERTION文に指定することはできません。アサーションを削除して再作成する必要があります。

関連項目:

SET CONSTRAINT[S]

INTIALLY句

INITIALLY句は、DEFERRABLEであるアサーションに対するデフォルトのチェック動作を指定します。INITIALLY設定は、後続のトランザクションにSET CONSTRAINT[S]文を指定することで上書きできます。

  • INITIALLY IMMEDIATEを指定すると、後続の各SQL文の最後にアサーションがチェックされます。INITIALLYを指定しない場合、INITIALLY IMMEDIATEがデフォルトになります。

  • INITIALLY DEFERREDを指定すると、後続のトランザクションの最後にアサーションがデータベースでチェックされます。

    制約をNOT DEFERRABLEとして宣言した場合、この句は無効です。NOT DEFERRABLE制約は自動的にINITIALLY IMMEDIATEになり、INITIALLY DEFERREDに変更することはできないためです。

ENABLE | DISABLE , VALIDATE | NOVALIDATE

アサーションには次の2つのプロパティがあります:

  • ENABLEまたはDISABLE

    このプロパティは、アサーションがDML文の実行に強制されるかどうかを決定します。

  • VALIDATEまたはNOVALIDATE

    このプロパティは、アサーションの関連する表内のすべてのデータが、現在そのアサーションに従っているかどうかを決定します。

これら2つのプロパティを組み合せると、次の4つの状態になります:

  • ENABLE VALIDATE

    すべてのデータがアサーションに従っています。これは、アサーションが有効なときに参照表の既存のデータが検証され、DML文によって行われた後続のデータ変更も検証されたということです。

  • ENABLE NOVALIDATE

    アサーションに従っていないデータが存在する可能性があります。これは、制約が有効なときに参照表の既存のデータは検証されていないが、DML文によって行われた後続のデータ変更は検証されたということです。

  • DISABLE VALIDATE

    参照表のすべてのデータがアサーションに従い、DML文に違反する可能性のある実行は許可されません。この設定は、Oracleリリース8.1で、特にデータ・ウェアハウス環境のパーティション表に対する制約のために導入されました。この状態はアサーションには適用されません。

  • DISABLE NOVALIDATE

    すべてのデータがアサーションに従うわけではなく、DML文の実行中にアサーションが強制されるわけではありません。

次のルールは、サーションでのプロパティENABLE/DISABLEおよびVALIDATE/NOVALIDATEをのデフォルト設定をア制御します。これは、制約の場合と同じ動作です:

  • どちらも指定されていない場合は、ENABLE/VALIDATEがデフォルトです。

  • ENABLEのみが指定されている場合は、VALIDATEがデフォルトです。

  • DISABLEのみが指定されている場合は、NOVALIDATEがデフォルトです。

  • VALIDATEのみが指定されている場合は、ENABLEがデフォルトです。

  • NOVALIDATEのみが指定されている場合は、ENABLEがデフォルトです。

制約のALL/USER/DBA_CONSTRAINTSと同様に、列STATUSおよびVALIDATEDはALL/USER/DBA_ASSERTIONSのプロパティを公開します。

  • USER_ASSERTIONS: 現在のスキーマが所有(作成)しているアサーションを表示します。

  • DBA_ASSERTIONS: 現在のPDBの任意のスキーマによって作成されたアサーションを表示します。

  • ALL_ASSERTIONS: 現在のスキーマが所有するアサーションと、現在のスキーマが少なくとも1つのオブジェクト権限(SELECT、INSERT、UPDATEまたはDELETE)を持つ表がアサーションに少なくとも1つ含まれる別のスキーマが所有するアサーションを示します。後者の場合、セキュリティ上の理由から、アサーションの定義は明らかにされません(つまり、NULLになります)。定義SQLには、現在のスキーマにオブジェクト権限がない他の表が含まれる場合があります。

アサーションは決定的である必要があります。アサーションの非決定的なファンクション・コールは許可されません。これには、 SYSDATE、SYSTIMESTAMP、SYS_CONTEXT、USERENV、USER、CURRENT_SCHEMA、CURRENT_USER、SESSION_USERが含まれますが、これらに限定されません。

アサーションで使用できないその他のSQL言語機能は次のとおりです:

  • DDLビュー

  • マテリアライズド・ビュー

  • 一時表

  • 仮想列

  • シノニム(アサーション内のすべてのオブジェクト参照は、ベース・スキーマ表への参照である必要があります)

  • GROUP BY、HAVINGおよび集計ファンクション

  • 外部結合

  • ANSI結合構文

  • スカラー副問合せ

  • 階層問合せ

  • 集合演算子

  • LATERALインライン・ビュー、クロス結合、クロス適用、外部適用

  • LOB、BFILE、LONG、JSONおよびADT列

  • 外部表

  • パーティション拡張句

  • 表のSAMPLE句

  • 行制限句

  • WITH句

  • (パイプライン化された)表ファンクションおよびCで記述されたファンクションを含むPL/SQLファンクション。これには、表にアクセスしない(決定的な)ファンクションが含まれています

  • 分析ファンクション

  • データベース・リンク

  • PIVOT、UNPIVOT

  • MODEL

  • MATCH RECOGNIZE

  • フラッシュバック問合せ

  • FETCH FIRST

  • JSON/XML/GRAPH表

  • オブジェクト・ビューのONLY

  • 定量化された比較述語(ANY、SOME、ALL)

アサーションは、デフォルトのREAD COMMITTED分離レベルでのみサポートされています。アサーションの検証は、分離レベルSERIALIZABLEではサポートされていません。

例

例1: 社長が存在する

次の文は、企業に社長が存在することを検証するアサーションを作成します:

CREATE ASSERTION IF NOT EXISTS company_must_have_a_president
CHECK (
  EXISTS (
    SELECT 'a president'
    FROM employees
    WHERE job_id = 'AD_PRES'
   )
);

このアサーションを作成するには、job_id = 'AD_PRES'が指定された表に少なくとも1つの行が存在する必要があります。そのような行が存在しない場合、文でエラーが発生し、アサーションは作成されません。

company_must_have_a_presidentという名前のオブジェクトがすでに存在する場合、この文はエラーなしで完了します。既存のオブジェクトは変更されず、このアサーションは作成されません。

例2: 重複する職務履歴がない

次の文は、スタッフが職務履歴で重複する日付がないことを検証するアサーションを作成します。ここでは、ビジネス・ルールを検証するために存在式が使用されます:

CREATE ASSERTION no_overlapping_job_history
CHECK (
  NOT EXISTS (
    SELECT 'overlapping job history'  
    FROM job_history jh1,
         job_history jh2
    WHERE jh1.employee_id = jh2.employee_id 
    AND jh1.ROWID <> jh2.ROWID
    AND jh1.start_date < jh2.end_date
    AND jh1.end_date > jh2.start_date
   )
);

次に、SATISFY句にブール式を含む汎用式を使用して記述された同等のアサーションを示します: 

CREATE ASSERTION no_overlapping_job_history
CHECK (
  ALL (
    SELECT jh1.start_date first_start, jh1.end_date first_end,
           jh2.start_date next_start, jh2.end_date next_end
    FROM job_history jh1, job_history jh2
    WHERE jh1.employee_id = jh2.employee_id
    AND jh1.ROWID <> jh2.ROWID
  ) jh
  SATISFY (
      jh.first_end <= jh.next_start
   OR jh.first_start >= jh.next_end
 )
);

ノート: このアサーションのどちらのバージョンも、開始日が終了日より前であることを確認するためのCHECK制約が存在すると想定しています。

例3: スタッフの収入はマネージャより少ない

次の文は、従業員の給与がマネージャの給与を下回っていることを確認するアサーションを作成します:

CREATE ASSERTION staff_earn_less_than_manager
CHECK (
  ALL (
    SELECT staff.salary staff_salary,
           mgr.salary manager_salary
    FROM hr.employees staff,
         hr.employees mgr
    WHERE staff.manager_id = mgr.employee_id
    AND staff.manager_id IS NOT NULL 
  ) staff
  SATISFY (
    staff_salary < manager_salary
  )
) NOVALIDATE;

これにより、HRスキーマのemployees表が参照され、現在のユーザーのスキーマにアサーションが作成されます。現在のユーザーがHRでない場合は、hr.employees表に対してASSERTION REFERENCES権限を直接付与する必要があります。

アサーションはNOVALIDATE状態で作成されます。つまり、マネージャの給与より大きい給与の従業員行が存在する可能性があるということです。アサーションが有効であるため、データベースはこのルールを今後のDML文に適用します。

例4: 従業員は給与範囲内で支給する必要がある

次に、すべての従業員の給与が職務に対して許可される最小および最大給与値内にあることを検証するアサーションを作成します:

CREATE ASSERTION hr.salary_within_job_limit
CHECK (
  ALL (
    SELECT job_id, salary
    FROM hr.employees
  ) emp
  SATISFY (
    EXISTS (
      SELECT 'salary within pay band'
      FROM hr.jobs job
      WHERE emp.job_id = job.job_id
      AND emp.salary BETWEEN job.min_salary AND job.max_salary
     )
  )
)
DISABLE;

このアサーションは、HRスキーマに作成されます。別のユーザーとして接続しているときにこのアサーションを実行するには、ユーザーにCREATE ANY ASSERTION権限またはCREATE ANY ASSERTION ON SCHEMA hr権限が必要です。

このアサーションはDISABLE状態で作成されるため、既存の行はチェックされず、このルールは今後のDML文にも適用されません。つまり、従業員の給与を職務の給与範囲外の値として保存できます。

例5: すべての部門に従業員が存在する

次の文は、すべての部門に少なくとも1人の従業員が存在することを確認するためのアサーションを作成します:

CREATE ASSERTION employee_in_every_dept
CHECK (
  ALL (
    SELECT d.department_id 
    FROM departments d
  ) d
  SATISFY (
    EXISTS (
      SELECT 'an employee' FROM employees e
      WHERE e.department_id = d.department_id
      AND e.department_id IS NOT NULL
    )
  )
)
DEFERRABLE INITIALLY DEFERRED;

デフォルトでは、アサーションは文レベルで検証されます。従業員から部門への外部キーがあると仮定すると、鶏が先か卵が先かの問題が発生します:

  • アサーションは、新しい部門を挿入できません

  • 外部キーでは、存在しない部門に従業員を割り当てることができません。

アサーションDEFERRABLE INITIALLY DEFERREDを宣言すると、コミット時間までアサーション検証が遅延します。これにより、新しい部門とその従業員を同じトランザクションに挿入できます。

例6: すべての部門に犯罪歴のない従業員が存在する必要がある

このアサーションでは、各部門に犯罪歴のない従業員が少なくとも1人存在していることが確認されます:

CREATE ASSERTION employee_without_criminal_record_in_every_dept
CHECK (
  ALL (
    SELECT d.department_id
    FROM departments d
  ) d
  SATISFY (
    EXISTS (
      SELECT 'an employee' FROM employees e
      WHERE e.department_id = d.department_id
      AND e.department_id IS NOT NULL
      AND NOT EXISTS (
        SELECT 'a criminal record'
        FROM criminal_records cr   
        WHERE cr.employee_id = e.employee_id
      )
    )
  )
)
DEFERRABLE INITIALLY DEFERRED;

これは、次のように存在式のみを使用してリライトできます:

CREATE ASSERTION employee_without_criminal_record_in_every_dept
CHECK (
  NOT EXISTS (
    SELECT 'a department'
    FROM departments d
    WHERE NOT EXISTS (
      SELECT 'an employee' FROM employees e
      WHERE e.department_id = d.department_id
      AND e.department_id IS NOT NULL
      AND NOT EXISTS (
        SELECT 'a criminal record'
        FROM criminal_records cr
        WHERE cr.employee_id = e.employee_id
      )
    )
  )
)
DEFERRABLE INITIALLY DEFERRED;

どちらの例も、ネストされた可能な[NOT] EXISTS述語の最大レベルを表します。存在式の場合は3、汎用式の場合は2です。

この例ではすべての部門に従業員が存在するため、DEFERRABLE INITIALLY DEFERRED状態でアサーションが作成され、新しい部門とその従業員を1つのトランザクションに挿入できます。

hrサンプル・スキーマでこれらのアサーションを作成するには、最初に次の表を作成します: 

CREATE TABLE criminal_records (
  employee_id INTEGER NOT NULL,
  conviction_date DATE NOT NULL,
  PRIMARY KEY ( employee_id, conviction_date )
);