5
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@kenwatanin日本オラクル株式会社

Oracle Database 26ai) Assertion（アサーション）で日付範囲の重複を防いでみた (2026/04/05)

5
Posted at

はじめに

Oracle AI Database 26ai では、Assertion（アサーション）という新しい整合性制約が導入されました。従来の CHECK 制約や UNIQUE 制約は単一行または単一テーブル内の制約しか表現できませんでしたが、Assertion を使うと 複数行・複数テーブルにまたがるビジネスルールをデータベースレベルで宣言的に強制できます。

日付範囲の重複防止はその典型的なユースケースのひとつです。たとえば「同じ従業員が同じ期間に複数の職歴レコードを持ってはいけない」というルールを、アプリケーション側の検証ロジックではなくデータベース自体に任せることができます。これにより、アプリケーションを経由しないバッチ処理や直接 DML 操作においても、データの整合性が保証されます。

Assertion には表現方法として主に 2 種類のスタイルがあります。

スタイル キーワード 用途
存在表現（Existential Expression） [NOT] EXISTS 「〇〇が存在する（しない）こと」を保証する
全称表現（Universal Expression） ALL ... SATISFY 「すべての行が条件を満たすこと」を保証する

日付範囲の重複防止には 全称表現（Universal Expression）を使います。

事前準備

  • Oracle AI Database 26ai 以降（Assertion は 26ai から導入された機能です）
  • Assertion を作成するスキーマに対して次のいずれかの権限が付与されていること
    • 自スキーマ内に作成する場合: CREATE ASSERTION システム権限
    • 他スキーマに作成する場合: CREATE ANY ASSERTION または CREATE ANY ASSERTION ON SCHEMA <スキーマ名>
    • 他スキーマのテーブルを参照する場合: 対象テーブルへの ASSERTION REFERENCES オブジェクト権限
  • トランザクションの分離レベルが READ COMMITTED であること（SERIALIZABLE では Assertion はサポートされません）
  • Oracle Autonomous Database または Oracle AI Database 26ai の環境に接続できること

手順

Step 1: 検証用テーブルの作成とデータ投入

まず、職歴を管理する job_history テーブルを作成します。従業員 ID・開始日・終了日・職種 ID を持つシンプルな構成です。

-- 検証用テーブルの作成
CREATE TABLE job_history (
    employee_id  NUMBER        NOT NULL,
    start_date   DATE          NOT NULL,
    end_date     DATE          NOT NULL,
    job_id       VARCHAR2(10)  NOT NULL,
    CONSTRAINT jh_pk PRIMARY KEY (employee_id, start_date)
);

重複しない正常データを投入します。

-- 正常なデータ（重複なし）
INSERT INTO job_history VALUES (101, DATE '2020-01-01', DATE '2021-12-31', 'IT_PROG');
INSERT INTO job_history VALUES (101, DATE '2022-01-01', DATE '2023-06-30', 'IT_MAN');
INSERT INTO job_history VALUES (102, DATE '2021-04-01', DATE '2023-03-31', 'SA_REP');
COMMIT;

Step 2: Assertion の作成（重複チェック）

次に、全称表現（Universal Expression）を使って「同一従業員の職歴期間が重複しないこと」を保証する Assertion を作成します。

CREATE ASSERTION no_overlapping_job_history
CHECK (
  ALL (
    SELECT jh1.start_date  AS first_start,
           jh1.end_date    AS first_end,
           jh2.start_date  AS next_start,
           jh2.end_date    AS 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
  )
);

Assertion created.

ロジックの解説:

  • ALL (SELECT ...) jh で、同じ従業員 (employee_id が同じ) の全レコードペアを列挙します。自己参照を避けるため ROWID <> ROWID で同一行を除外しています。
  • SATISFY 節で「1 件目の終了日 ≦ 2 件目の開始日、または 1 件目の開始日 ≧ 2 件目の終了日」という条件を検証します。この条件が 成立しない ペア（＝重複している）が存在する場合、トランザクションはエラーになります。

注意: CREATE ASSERTION のデフォルト動作は ENABLE VALIDATE です。つまり、実行時点で既存データが条件を満たしていないとエラーになります。既存の不整合データを後で修正したい場合は NOVALIDATE オプションを使用してください。

参考）既存データを検証せずに Assertion を作成したい場合

CREATE ASSERTION no_overlapping_job_history
CHECK (
  ALL (
    SELECT jh1.start_date  AS first_start,
           jh1.end_date    AS first_end,
           jh2.start_date  AS next_start,
           jh2.end_date    AS 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
  )
) NOVALIDATE;

Step 3: Assertion の確認

作成した Assertion のメタデータを確認します。

-- 自スキーマの Assertion 一覧
SELECT assertion_name,
       status,
       validated,
       deferrable,
       deferred
FROM   user_assertions;

ASSERTION_NAME                 STATUS   VALIDATED     DEFERRABLE     DEFERRED
------------------------------ -------- ------------- -------------- ---------
NO_OVERLAPPING_JOB_HISTORY     ENABLED  VALIDATED     NOT DEFERRABLE IMMEDIATE

どのテーブルが Assertion のトリガーとなるかは USER_ASSERTION_DEPENDENCIES で確認できます。

-- Assertion が依存するテーブルの確認
SELECT assertion_name,
       referenced_name,
       referenced_type,
       validation_event
FROM   user_assertion_dependencies;

ASSERTION_NAME                 REFERENCED_NAME REFERENCED_TYPE         VALIDATION_EVENT
------------------------------ --------------- ----------------------- ------------------------
NO_OVERLAPPING_JOB_HISTORY     JOB_HISTORY     TABLE                   ROWS INSERTED OR UPDATED
NO_OVERLAPPING_JOB_HISTORY     JOB_HISTORY     TABLE                   ROWS INSERTED OR UPDATED

Step 4: 重複データの INSERT を試みる（動作確認）

Assertion が正しく機能するか確認するため、既存レコードと期間が重複するデータを INSERT してみます。
Assertion 違反のため、次のエラーが返ることを確認します。

-- 従業員 101 の既存期間（2020-01-01〜2021-12-31）と重複するレコード
INSERT INTO job_history VALUES (101, DATE '2021-06-01', DATE '2022-06-30', 'SA_MAN');

INSERT INTO job_history VALUES (101, DATE '2021-06-01', DATE '2022-06-30', 'SA_MAN')
            *
ERROR at line 1:
ORA-08601: SQL assertion (TEST_USER.NO_OVERLAPPING_JOB_HISTORY) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/

注意: エラーメッセージに表示されるスキーマ名は、Assertion を作成したユーザーのスキーマ名になります。

重複しない期間のデータは正常に登録できることも確認します。

-- 重複しない期間のデータ（問題なく INSERT できること）
INSERT INTO job_history VALUES (101, DATE '2024-01-01', DATE '2025-03-31', 'SA_MAN');

1 row created.

COMMIT;

Commit complete.

Step 5: DEFERRABLE Assertion の活用（応用）

一括ロードやデータ修正のように、トランザクション途中で一時的に整合性が崩れることが許容される場面では、DEFERRABLE INITIALLY DEFERRED オプションが有効です。コミット時にのみ検証が行われるため、複数の DML をまとめて実行できます。

-- コミット時のみ検証する DEFERRABLE Assertion の例
CREATE ASSERTION no_overlapping_job_history_deferred
CHECK (
  ALL (
    SELECT jh1.start_date  AS first_start,
           jh1.end_date    AS first_end,
           jh2.start_date  AS next_start,
           jh2.end_date    AS 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
  )
) DEFERRABLE INITIALLY DEFERRED;

一時的に特定の Assertion のチェックを遅延・即時に切り替えることもできます。

-- 特定 Assertion を即時チェックに切り替える
SET CONSTRAINT no_overlapping_job_history_deferred IMMEDIATE;

-- 特定 Assertion をコミット時チェックに戻す
SET CONSTRAINT no_overlapping_job_history_deferred DEFERRED;

-- すべての DEFERRABLE Assertion をコミット時チェックに切り替える
SET CONSTRAINTS ALL DEFERRED;

動作確認: DEFERRABLE の効果を確認する

INITIALLY DEFERRED の場合、トランザクション中は重複データも一時的に許容され、COMMIT 時にまとめて検証されます。

-- DEFERRED モードのまま重複データを INSERT → この時点ではエラーにならない
INSERT INTO job_history VALUES (101, DATE '2021-06-01', DATE '2022-06-30', 'SA_MAN');

-- COMMIT 時に Assertion が評価され、重複違反としてエラーになる
COMMIT;
-- ORA-08601: SQL assertion (<スキーマ名>.NO_OVERLAPPING_JOB_HISTORY_DEFERRED) violated.

SQL>  INSERT INTO job_history VALUES (101, DATE '2021-06-01', DATE '2022-06-30', 'SA_MAN');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-08601: SQL assertion (TEST_USER.NO_OVERLAPPING_JOB_HISTORY) violated.
Help: https://docs.oracle.com/error-help/db/ora-02091/

SET CONSTRAINT ... IMMEDIATE で即時チェックに切り替えると、INSERT 時点でエラーが発生します。

-- 即時チェックモードに切り替える
SET CONSTRAINT no_overlapping_job_history_deferred IMMEDIATE;

-- 重複データの INSERT → この時点でエラーになる
INSERT INTO job_history VALUES (101, DATE '2021-06-01', DATE '2022-06-30', 'SA_MAN');
-- ORA-08601: SQL assertion (<スキーマ名>.NO_OVERLAPPING_JOB_HISTORY_DEFERRED) violated.

SQL> SET CONSTRAINT no_overlapping_job_history_deferred IMMEDIATE;

Constraint set.

SQL> INSERT INTO job_history VALUES (101, DATE '2021-06-01', DATE '2022-06-30', 'SA_MAN');
INSERT INTO job_history VALUES (101, DATE '2021-06-01', DATE '2022-06-30', 'SA_MAN')
*
ERROR at line 1:
ORA-08601: SQL assertion (TEST_USER.NO_OVERLAPPING_JOB_HISTORY_DEFERRED) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/

Step 6: Assertion の有効・無効の切り替え

メンテナンス等で一時的に Assertion を無効化したい場合は ALTER ASSERTION を使用します。

-- Step 5 で作成した DEFERRABLE Assertion も合わせて無効化する
ALTER ASSERTION no_overlapping_job_history DISABLE;
ALTER ASSERTION no_overlapping_job_history_deferred DISABLE;

INSERT INTO job_history VALUES (101, DATE '2021-06-01', DATE '2022-06-30', 'SA_MAN');
COMMIT;
-- → 正常にコミットされる（両方の Assertion が無効なため）

-- ENABLE VALIDATE で再有効化 → 既存の重複データがあるためエラーになる
ALTER ASSERTION no_overlapping_job_history ENABLE VALIDATE;
-- ORA-08601: SQL assertion (<スキーマ名>.NO_OVERLAPPING_JOB_HISTORY) violated.

-- 不整合データを先に修正してから ENABLE VALIDATE する
DELETE FROM job_history
WHERE employee_id = 101
  AND start_date = DATE '2021-06-01';
COMMIT;

ALTER ASSERTION no_overlapping_job_history ENABLE VALIDATE;
-- → 既存データに重複がなければ正常に有効化される

-- 以降の重複 INSERT は再びブロックされることを確認
INSERT INTO job_history VALUES (101, DATE '2021-06-01', DATE '2022-06-30', 'SA_MAN');
-- ORA-08601: SQL assertion (<スキーマ名>.NO_OVERLAPPING_JOB_HISTORY) violated.

実行例）

SQL> ALTER ASSERTION no_overlapping_job_history DISABLE;

Assertion altered.

SQL> ALTER ASSERTION no_overlapping_job_history_deferred DISABLE;

Assertion altered.

SQL>
SQL> INSERT INTO job_history VALUES (101, DATE '2021-06-01', DATE '2022-06-30', 'SA_MAN');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> ALTER ASSERTION no_overlapping_job_history ENABLE VALIDATE;
ALTER ASSERTION no_overlapping_job_history ENABLE VALIDATE
*
ERROR at line 1:
ORA-08601: SQL assertion (TEST_USER.NO_OVERLAPPING_JOB_HISTORY) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/


SQL> DELETE FROM job_history
  2  WHERE employee_id = 101
  AND start_date = DATE '2021-06-01';
  3
1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> ALTER ASSERTION no_overlapping_job_history ENABLE VALIDATE;

Assertion altered.


SQL> INSERT INTO job_history VALUES (101, DATE '2021-06-01', DATE '2022-06-30', 'SA_MAN');
INSERT INTO job_history VALUES (101, DATE '2021-06-01', DATE '2022-06-30', 'SA_MAN')
            *
ERROR at line 1:
ORA-08601: SQL assertion (TEST_USER.NO_OVERLAPPING_JOB_HISTORY) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/

Step 7: クリーンアップ

検証が完了したら、作成したオブジェクトを削除します。

-- Assertion の削除
DROP ASSERTION no_overlapping_job_history;
DROP ASSERTION no_overlapping_job_history_deferred;

-- テーブルの削除
DROP TABLE job_history PURGE;

制限事項

Assertion の CHECK 条件に使用できる SQL には多くの制限があります。代表的なものを以下にまとめます。

カテゴリ 使用できない機能
集計・グループ化 GROUP BY, HAVING, 集計関数（COUNT, SUM 等）
結合構文 外部結合、ANSI JOIN 構文（JOIN ... ON 形式）
サブクエリ スカラーサブクエリ、横方向インラインビュー、WITH 句
集合演算 UNION, MINUS, INTERSECT
解析関数 ウィンドウ関数（ROW_NUMBER, RANK 等）
非決定的関数 SYSDATE, SYSTIMESTAMP, SYS_CONTEXT, USER 等
PL/SQL 関数 ユーザー定義関数（決定的関数を含む）
オブジェクト種別 ビュー、マテリアライズドビュー、一時テーブル、外部テーブル、仮想列、シノニム
データ型 LOB, BFILE, LONG, JSON, ADT 型の列
その他 行数制限句（FETCH FIRST）、フラッシュバッククエリ、PIVOT, MODEL, MATCH_RECOGNIZE 等
分離レベル SERIALIZABLE トランザクションでは使用不可（READ COMMITTED のみ対応）
ネスト制限 存在表現は最大 3 段、全称表現の SATISFY 内は最大 2 段

注意: パフォーマンスへの影響に注意が必要です。DML のたびに Assertion の条件が評価されるため、大量データの一括処理では実行時間が長くなる場合があります。USER_ASSERTION_LOCK_MATRIX でロックの状況を確認することを推奨します。

おわりに

以下の内容が確認できました。

  • Oracle AI Database 26ai の CREATE ASSERTION を使うと、複数行にまたがる整合性ルール（日付範囲の重複防止など）をデータベースレベルで宣言的に定義できます。
  • 全称表現（ALL ... SATISFY）により、「すべてのレコードペアが重複していないこと」を自己結合で簡潔に記述できます。
  • DEFERRABLE INITIALLY DEFERRED を指定すれば、一括ロードなどトランザクション途中で整合性が崩れる場面にも柔軟に対応できます。
  • ALTER ASSERTION DISABLE / ENABLE でメンテナンス時の一時無効化も容易です。
  • Assertion の条件式には多くの SQL 機能が使えないため、複雑なルールを表現する際は制限事項を事前に確認する必要があります。

参考情報

5
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?