参照整合性制約に違反したデータを確認する方法

公開日: 公開日：2016年11月14日（月）
更新日: 更新日：2020年07月08日（水）
サブカテゴリー: 基本操作

タグ: #制約

参照整合性制約を設定したテーブルを作成することがきます。
しかし、データロードや更新時には参照整合性のチェックは行われません。
そのため、データロード後に関数を使用して参照整合性違反のデータがないかチェックをする必要があります。

以下にINSERT後に参照整合性違反を確認する方法をご紹介します。

実行例

参照整合性に違反したデータがないか確認する実行例を以下に示します。

関数の構文

ANALYZE_CONSTRAINTS関数を使用し参照整合性をチェックします。

dbadmin=> SELECT ANALYZE_CONSTRAINTS('スキーマ名.テーブル名','列名');

※列名は省略可能です。

テーブル構成

LINEORDERテーブルのLO_CUSTKEY列に参照整合性制約を定義しています。

データ内容

CUSTOMERテーブルとLINEORDERテーブルは参照整合性に違反していないデータ内容となっています。

INSERTしたデータが参照整合性の違反をしていない場合

１．INSERTを実行
dbadmin=> insert into lineorder values(17894054,1,3,2,4130730,71527,8,19950404);					
 OUTPUT					
--------					
1					
(1 row)					

=> 正常にINSERTされます。

２．ANALYZE_CONSTRAINTS関数を使用して参照整合性に違反しているか確認
dbadmin=> select analyze_constraints('public.lineorder');
 Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values
-------------+------------+--------------+-----------------+-----------------+---------------
(0 rows)

=> 関数の実行結果がゼロ件で参照整合性に違反したデータは存在しません。

３．参照整合性に違反したデータが存在しないためCOMMITを実行
dbadmin=> commit;
COMMIT

INSERTしたデータが参照整合性の違反をしている場合

１．INSERTを実行
（CUSTOMER.C_CUSTKEYに存在しないデータ('4')をLINEORDER.LO_CUSTKEYにINSERT）
　　　　　　　　　　　　　　　　　　　　　　　　　        ↓
dbadmin=> insert into lineorder values(17894054,1,4,2,4130730,71527,8,19950404);
 OUTPUT
--------
      1
(1 row)

=> 正常にINSERTされます。

２．ANALYZE_CONSTRAINT関数を使用して参照整合性に違反しているか確認
dbadmin=> select analyze_constraints('public.lineorder');
 Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values
-------------+------------+--------------+-----------------+-----------------+---------------
 public      | LINEORDER  | LO_CUSTKEY   | C_FOREIGN       | FOREIGN         | ('4')　<== ★参照整合性に違反しているデータ
(1 row)

=> 関数の実行結果に参照整合性に違反しているデータが出力されます。
   LINEORDERのLC_CUSTKEY列(外部キー)に参照整合性の違反をしているデータ('4')が存在しています。

３．参照整合性に違反したデータが存在するためROLLBACKを実行
dbadmin=> rollback;
ROLLBACK

検証バージョンについて

この記事の内容はVertica 9.1で確認しています。

Analytics Database