主キー/一意キー制約による重複データの排除

公開日: 公開日：2015年11月14日（土）
更新日: 更新日：2025年12月17日（水）
サブカテゴリー: アーキテクチャ; 基本操作

タグ: #制約

はじめに

Vertica7.2 より、「主キー/一意キー制約」による重複データの排除が可能になりました。本機能を有効にした場合、Vertica7.1 と比べて以下の動作に違いがあります

・COPY, INSERT 文などDML 実行時に「主キー/一意キー制約」による重複データの追加を自動で防ぐことができます。
・データ挿入後の「ANALYZE_CONSTRAINTS」コマンドによるキーの重複確認が不要になります。Vertica7.1 でも「主キー／一意キー制約」を設定することはできましたが、重複データの追加は可能でした。

主キー/一意キーの有効化

テーブルを新規に作成した場合の動作を「テーブル単位」もしくは「データベース単位」で指定することができます。

テーブル単位で設定する場合

CREATE TABLE 文のオプションとして、「テーブル単位」で設定します。

dbadmin=> CREATE TABLE test_tbl (EMPNO INT PRIMARY KEY ENABLED, ENAME VARCHAR(10));
「ENABLED」：重複データ排除有効
「DISABLED」：重複データ排除無効

データベースで設定する場合

データベースのデフォルト値を変更します。CREATE TABLE 文のオプションを設定する必要はなく、データベースのデフォルト値が採用されます。

dbadmin=> ALTER DATABASE SSBM SET EnableNewPrimaryKeysByDefault=1;
「EnableNewPrimaryKeysByDefault/EnableNewUniqueKeysByDefault=1」：デフォルトの重複データ排除有効
「EnableNewPrimaryKeysByDefault/EnableNewUniqueKeysByDefault=0」：デフォルトの重複データ排除無効　※ データベース作成時点では「0」が設定されています。
※ 変更はオンラインで可能です。データベースの再起動は不要です。

実行例

EMPNO 列が主キーのテーブルに、重複データを格納した場合の実行例をご紹介します。

CREATE TABLE 文のオプション使用例

1) CREATE TABLE 文のオプションを使用

//EMPNO 列＝制約: 主キー、重複データ排除: 有効を設定
dbadmin=> CREATE TABLE test_tbl (EMPNO INT PRIMARY KEY ENABLED, ENAME VARCHAR(10));
CREATE TABLE
dbadmin=> \d test_tbl;
                                     List of Fields by Tables
 Schema |  Table   | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+----------+--------+-------------+------+---------+----------+-------------+-------------
 public | test_tbl | EMPNO  | int         |    8 |         | t        | t           |
 public | test_tbl | ENAME  | varchar(10) |   10 |         | f        | f           |
(2 rows)

2) 重複データ挿入時の動作

//テストデータの追加
dbadmin=> insert into test_tbl values(1,'a');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test_tbl values(2,'b');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test_tbl values(3,'c');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from test_tbl;
 EMPNO | ENAME
-------+-------
     1 | a
     2 | b
     3 | c
(3 rows)

//エラー発生
dbadmin=> insert into test_tbl values(1,'d');
ERROR 6745:  Duplicate key values: 'EMPNO=1' -- violates constraint 'public.test_tbl.C_PRIMARY'

データベースパラメータの使用例

1) CREATE TABLE 文のオプションは未設定

//EMPNO列＝制約: 主キー、重複データ排除: 未設定
dbadmin=> CREATE TABLE test_tbl2 (EMPNO INT PRIMARY KEY, ENAME VARCHAR(10));
CREATE TABLE
dbadmin=> \d test_tbl2
                                     List of Fields by Tables
 Schema |   Table   | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-----------+--------+-------------+------+---------+----------+-------------+-------------
 public | test_tbl2 | EMPNO  | int         |    8 |         | t        | t           |
 public | test_tbl2 | ENAME  | varchar(10) |   10 |         | f        | f           |
(2 rows)

2) 重複データ挿入時の動作

//テストデータの追加
dbadmin=> insert into test_tbl2 values(1,'a');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test_tbl2 values(2,'b');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test_tbl2 values(3,'c');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from test_tbl2;
 EMPNO | ENAME
-------+-------
     1 | a
     2 | b
     3 | c
(3 rows)

//重複データ追加可能
dbadmin=> insert into test_tbl2 values(1,'a');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from test_tbl2;
 EMPNO | ENAME
-------+-------
     1 | a
     2 | b
     3 | c
     1 | a
(4 rows)

3) データベースパラメータの変更

//重複データ排除のデフォルト値を変更
dbadmin=> ALTER DATABASE SSBM SET EnableNewPrimaryKeysByDefault=1;
ALTER DATABASE

4) CREATE TABLE 文のオプションは未設定

//EMPNO列＝制約: 主キー、重複データ排除: 未設定
dbadmin=> CREATE TABLE test_tbl3 (EMPNO INT PRIMARY KEY, ENAME VARCHAR(10));
CREATE TABLE
dbadmin=> \d test_tbl3
                                     List of Fields by Tables
 Schema |   Table   | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-----------+--------+-------------+------+---------+----------+-------------+-------------
 public | test_tbl3 | EMPNO  | int         |    8 |         | t        | t           |
 public | test_tbl3 | ENAME  | varchar(10) |   10 |         | f        | f           |
(2 rows)

5) 重複データ挿入時の動作

//テストデータの追加
dbadmin=> insert into test_tbl3 values(1,'a');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test_tbl3 values(2,'b');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test_tbl3 values(3,'c');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from test_tbl3;
 EMPNO | ENAME
-------+-------
     1 | a
     2 | b
     3 | c
(3 rows)

//エラー発生
dbadmin=> insert into test_tbl3 values(1,'d');
ERROR 6745:  Duplicate key values: 'EMPNO=1' -- violates constraint 'public.test_tbl3.C_PRIMARY'

一意性チェックが行われる処理

本機能を有効化した場合に、以下の処理に対して、一意性チェックが行われます。

・「INSERT 文」：単一行の挿入およびSELECT パラメータを含むINSERT 文
・「バルクロード」： COPY 文を使用するバルクロード
・「UPDATE もしくはMERGE 文」：すべてのUPDATE およびMERGE 文
・「メタ関数」： MOVE_PARTITIONS_TO_TABLE およびSWAP_PARTITIONS_BETWEEN_TABLES
・「ALTER TABLE 文」：すでにデータをもつ既存のテーブルに対しADD CONSTRAINT もしくはALTER CONSTRAINT パラメータを含む文

参考情報

以下の記事では、Vertica 9.1 で主キーを設定した場合の動作について、ご紹介しています。ご参考までに、あわせてご確認ください。

・テーブル内の重複データを確認する方法
https://www.ashisuto.co.jp/cm/analytics-database/check-overlap-data.html

検証バージョンについて

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

Analytics Database