17.10 - 参照制約の関係における実テーブルの整合性の評価 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ定義言語 詳細トピック

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
2021年7月
Content Type
プログラミング リファレンス
Publication ID
B035-1184-171K-JPN
Language
日本語 (日本)

テーブルに強制されない参照制約を指定する場合、そのテーブルの参照制約を確認または評価する責任は、もっぱらユーザーにあります。宣言制約が適切な列定義内に宣言されていないため、テーブル間で参照制約の関係を指定するとき、システムは参照制約を施行しません。

整合性の保証という点からすれば、標準またはバッチ参照制約の宣言を使用しないでテーブルの参照整合性を保証する最善の方法は、関係するテーブルへの挿入、更新、および削除を処理するトリガーの集合などの一連の手続きによる制約を使用することです。

例えば、参照整合性を施行するために親テーブル上にDELETE/UPDATEトリガー、子テーブル上にINSERT/UPDATEトリガーを作成する場合があります。宣言制約が手続きによる制約よりも望ましい理由については、<Teradata Vantage™ - データベースの設計、B035-1094>で簡単に説明されています。

また、アクティブに起動しているトリガーは、置換する予定の単純な宣言制約よりも、システム パフォーマンスにさらに悪い影響を与える可能性があります。

参照整合性制約のいずれの形式も施行しない場合には、参照制約違反が生じる時間と場所を検出できる一連の評価手順を施行することを強くお勧めします。

以下のシナリオでは、基本SQLクエリーを使用して、参照整合性ルールの違反についてテーブル集合に問合わせます。

テーブルpk_tbl1およびsoftri_tbl1を作成して、softri_tbl1の列b2にpk_tbl1のa1列への参照制約の関係を持たせるとします。この関係では、テーブルpk_tbl1は親テーブルであり、softri_tbl1は子テーブルです。これらのテーブルを定義するためのDDLは、以下のCREATE TABLE文のようになります。

     CREATE TABLE pk_tbl1 (
       a1 INTEGER NOT NULL PRIMARY KEY,
       a2 INTEGER);

     CREATE TABLE softri_tbl1 (
       b1 INTEGER,
       b2 INTEGER CONSTRAINT softri_1
          REFERENCES WITH NO CHECK OPTION pk_tbl1(a1));

列softri_tbl1.b2は、プライマリ キー列pk_tbl1.a1を参照する暗黙的外部キーです。

ここで、以下のようにテーブルにデータを入れます。

     INSERT INTO pk_tbl1 (a1, a2) VALUES (11, 111);
     INSERT INTO pk_tbl1 (a1, a2) VALUES (22, 222);

     INSERT INTO softri_tbl1 (b1, b2) VALUES (100, 11);
     INSERT INTO softri_tbl1 (b1, b2) VALUES (200, 22);
     INSERT INTO softri_tbl1 (b1, b2) VALUES (300, 33);
     INSERT INTO softri_tbl1 (b1, b2) VALUES (400, 44);

softri_tbl1.b2の個別の値の集合がpk_tbl1の値の集合と同一である必要があるため(プライマリ キー値の集合として、デフォルトでは個別の集合を構成する)、テーブルsoftri_tbl1への3番目と4番目の挿入は、テーブル間での暗黙の参照整合性の関係に違反しています。このシナリオでは、softri_tbl1.b2値33および44は、暗黙の参照整合性の関係に違反する行を識別します。

以下のSELECT文は、このタイプの破壊をテストするための一般的なクエリーです。表示される値を判別できないため、外部キーのnullの除外がクエリーに含まれます。

     SELECT DISTINCT childtable.*
     FROM childtable, parenttable
     WHERE childtable.fk NOT IN (SELECT pk
                                 FROM parenttable)
     AND   childtable.fk IS NOT NULL;

このシナリオの場合、以下の対応のセットが定義されています。

一般的なクエリーの要素 このクエリーの要素
childtable softri_tbl1
parenttable pk_tbl1
childtable.fk softri_tbl1.b2

これらの対応から、このシナリオ内の破壊行をテストするために指定するクエリーは、以下のSELECT文のようになります。

     SELECT DISTINCT softri_tbl1.*
     FROM softri_tbl1, pk_tbl1
     WHERE softri_tbl1.b2 NOT IN (SELECT a1
                                  FROM pk_tbl1)
     AND   softri_tbl1.b2 IS NOT NULL;

      *** Query completed. 2 rows found. 2 columns returned.
      *** Total elapsed time was 1 second.

              b1           b2
     -----------  -----------
             300           33
             400           44

このクエリーによって生成されたレポートは、暗黙の外部キー値33および44を持つ行(予期されていた行)を返します。 これは、子テーブルの外部キー値が親テーブルのプライマリ キー値の集合の要素と一致しない行の集合です。 この一致しない行の集合は、データベースの参照整合性を維持するために子テーブルから削除する必要があります。

定期的に、特に、暗黙の関係にあるテーブルに大幅な更新が行なわれた後に、この評価のクエリーを実行してください。

このクエリーを実行する間隔全体でデータが破損する可能性があり、検出された問題を修復するという負担が生じます。クエリーは、データベースに参照整合性を強制しないことの問題を解決しません。クエリーの機能は、親テーブルにある1つのプライマリ キーまたは代替キーおよび子テーブルにある1つの外部キーが侵害されたかどうかの検出だけです。強制されないそれぞれの参照整合性関係は、違反を検出するために定期的に検査する必要があります。