目次
はじめに
Vertica7.2 より、「主キー/一意キー制約」 による重複データの排除が可能になりました。本機能を有効にした場合、Vertica7.1 と比べて以下の動作に違いがあります・COPY, INSERT 文などDML 実行時に「主キー/一意キー制約」 による重複データの追加を自動で防ぐことができます。
・データ挿入後の「ANALYZE_CONSTRAINTS」 コマンドによるキーの重複確認が不要になります。Vertica7.1 でも「主キー／一意キー制約」 を設定することはできましたが、重複データの追加は可能でした。
・データ挿入後の「ANALYZE_CONSTRAINTS」 コマンドによるキーの重複確認が不要になります。Vertica7.1 でも「主キー／一意キー制約」 を設定することはできましたが、重複データの追加は可能でした。
主キー/一意キーの有効化
テーブルを新規に作成した場合の動作を「テーブル単位」 もしくは「データベース単位」 で指定することができます。テーブル単位で設定する場合
CREATE TABLE 文のオプションとして、「テーブル単位」 で設定します。dbadmin=> CREATE TABLE test_tbl (EMPNO INT PRIMARY KEY ENABLED, ENAME VARCHAR(10));
「ENABLED」 ： 重複データ排除有効
「DISABLED」： 重複データ排除無効
「ENABLED」 ： 重複データ排除有効
「DISABLED」： 重複データ排除無効
データベースで設定する場合
データベースのデフォルト値を変更します。CREATE TABLE 文のオプションを設定する必要はなく、データベースのデフォルト値が採用されます。dbadmin=> ALTER DATABASE SSBM SET EnableNewPrimaryKeysByDefault=1;
「EnableNewPrimaryKeysByDefault/EnableNewUniqueKeysByDefault=1」： デフォルトの重複データ排除有効
「EnableNewPrimaryKeysByDefault/EnableNewUniqueKeysByDefault=0」： デフォルトの重複データ排除無効 ※ データベース作成時点では「0」 が設定されています。
※ 変更はオンラインで可能です。データベースの再起動は不要です。
「EnableNewPrimaryKeysByDefault/EnableNewUniqueKeysByDefault=1」： デフォルトの重複データ排除有効
「EnableNewPrimaryKeysByDefault/EnableNewUniqueKeysByDefault=0」： デフォルトの重複データ排除無効 ※ データベース作成時点では「0」 が設定されています。
※ 変更はオンラインで可能です。データベースの再起動は不要です。
実行例
EMPNO 列が主キーのテーブルに、重複データを格納した場合の実行例をご紹介します。CREATE TABLE 文のオプション使用例
1) CREATE TABLE 文のオプションを使用|
1 2 3 4 5 6 7 8 9 10 |
//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) 重複データ挿入時の動作
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
//テストデータの追加 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 文のオプションは未設定|
1 2 3 4 5 6 7 8 9 10 |
//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) 重複データ挿入時の動作
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
//テストデータの追加 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) データベースパラメータの変更
|
1 2 3 |
//重複データ排除のデフォルト値を変更 dbadmin=> ALTER DATABASE SSBM SET EnableNewPrimaryKeysByDefault=1; ALTER DATABASE |
4) CREATE TABLE 文のオプションは未設定
|
1 2 3 4 5 6 7 8 9 10 |
//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) 重複データ挿入時の動作
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
//テストデータの追加 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 パラメータを含む文
・「バルクロード」： COPY 文を使用するバルクロード
・「UPDATE もしくはMERGE 文」： すべてのUPDATE およびMERGE 文
・「メタ関数」 ： MOVE_PARTITIONS_TO_TABLE およびSWAP_PARTITIONS_BETWEEN_TABLES
・「ALTER TABLE 文」： すでにデータをもつ既存のテーブルに対しADD CONSTRAINT もしくはALTER CONSTRAINT パラメータを含む文
参考情報
以下の記事では、Vertica 9.1 で主キーを設定した場合の動作について、ご紹介しています。ご参考までに、あわせてご確認ください。・テーブル内の重複データを確認する方法
http://vertica-tech.ashisuto.co.jp/check-overlap-data/
