チェック制約の設定方法

1 はじめに
2 構文
3 制約の確認方法
4 実行例
5 参考情報
6 検証バージョンについて

はじめに

Vertica7.2以降、チェック制約の設定が可能となっています。チェック制約を使用することで条件を指定し、条件を満たさないデータの追加を禁止できるため、データの整合性が保証されます。チェック制約は、以下の処理に対しておこなわれます。

– INSERT文
– UPDATE文
– MERGE文
– COPY文
– COPY_PARTITIONS_TO_TABLEメタ関数
– MOVE_PARTITIONS_TO_TABLEメタ関数
– SWAP_PARTITIONS_BETWEEN_TABLESメタ関数

構文

・チェック制約を有効にします。

dbadmin=> ALTER DATABASE testdb SET EnableNewCheckConstraintsByDefault=1;

1	dbadmin=> ALTER DATABASE testdb SET EnableNewCheckConstraintsByDefault=1;

※データベース作成時点では、デフォルト「1」(有効)が設定されています。
※変更はオンラインで可能であり、データベースの再起動は不要です。

・「QUANTITY列に0より大きい値」のみを追加できるように、チェック制約を作成します。制約の名前は「chk_pos_quant」です。

dbadmin=> CREATE TABLE chk_test( ORDER_DATE timestamp, ITEM_CD int, QUANTITY int, PRICE int, CONSTRAINT chk_pos_quant CHECK (QUANTITY > 0);

1	dbadmin=> CREATE TABLE chk_test( ORDER_DATE timestamp, ITEM_CD int, QUANTITY int, PRICE int, CONSTRAINT chk_pos_quant CHECK (QUANTITY > 0);

※テーブル単位でチェック制約の「有効/無効」を制御したい場合は、CREATE TABLEのオプションで「ENABLED/DISABLED」を指定します。

制約の確認方法

作成したチェック制約は「table_constraints」もしくは「constraint_columns」システムテーブルから、定義情報を確認できます。

//table_constraints の場合
dbadmin=> select * from TABLE_CONSTRAINTS;
   constraint_id   | constraint_name | constraint_schema_id | constraint_key_count | foreign_key_count |     table_id      | table_name | foreign_table_id | constraint_type | is_enabled |        predicate
-------------------+-----------------+----------------------+----------------------+-------------------+-------------------+------------+------------------+-----------------+------------+--------------------------
 45035996273724572 | chk_pos_quant   |    45035996273704978 |                    1 |                 0 | 45035996273724570 | chk_test2  |                0 | c               | f          | (chk_test2.QUANTITY > 0)
 45035996273725072 | chk_pos_quant   |    45035996273704978 |                    1 |                 0 | 45035996273725070 | chk_test3  |                0 | c               | t          | (chk_test3.QUANTITY > 0)
 45035996273725100 | chk_pos_quant   |    45035996273704978 |                    1 |                 0 | 45035996273725098 | chk_test4  |                0 | c               | f          | (chk_test4.QUANTITY > 0)
 45035996273725662 | chk_pos_quant   |    45035996273704978 |                    1 |                 0 | 45035996273725660 | chk_test   |                0 | c               | t          | (chk_test.QUANTITY > 0)
(4 rows)

//table_constraints の場合

dbadmin=> select * from TABLE_CONSTRAINTS;

-------------------+-----------------+----------------------+----------------------+-------------------+-------------------+------------+------------------+-----------------+------------+--------------------------

45035996273724572 | chk_pos_quant | 45035996273704978 | 1 | 0 | 45035996273724570 | chk_test2 | 0 | c | f | (chk_test2.QUANTITY > 0)

45035996273725072 | chk_pos_quant | 45035996273704978 | 1 | 0 | 45035996273725070 | chk_test3 | 0 | c | t | (chk_test3.QUANTITY > 0)

45035996273725100 | chk_pos_quant | 45035996273704978 | 1 | 0 | 45035996273725098 | chk_test4 | 0 | c | f | (chk_test4.QUANTITY > 0)

45035996273725662 | chk_pos_quant | 45035996273704978 | 1 | 0 | 45035996273725660 | chk_test | 0 | c | t | (chk_test.QUANTITY > 0)

(4 rows)

項目	説明
constraint_id	制約を識別するために、Verticaカタログによって割り当てられた一意の番号
constraint_name	制約の名前（UNIQUE、FOREIGN KEY、NOT NULL、PRIMARY KEY、CHECKを指定した場合）
constraint_schema_id	制約を含むスキーマを識別するために、Verticaカタログによって割り当てられた一意の番号
constraint_key_count	制約キーの数
foreign_key_count	外部キーの数
table_id	テーブルを識別するために、Verticaカタログによって割り当てられた一意の番号
table_name	制約を含むテーブルの名前
foreign_table_id	外部キー制約で参照される外部表の一意のオブジェクト番号（外部キー制約でない場合はゼロ）
constraint_type	制約のタイプを示します。 c — check f — foreign p — primary u — unique
is_enabled	主キー、一意キー、チェック制約が使用可能かどうかを示します。 t — 使用可能 f — 使用不可
predicate	チェック制約で設定したSQL式

実行例

QUANTITY列にチェック制約を設定したテーブルに対して、制約に違反したデータを追加した場合の実行例をご紹介します。

dbadmin=> ALTER DATABASE testdb SET EnableNewCheckConstraintsByDefault=1;
ALTER DATABASE
dbadmin=> select current_value, restart_value,database_value, default_value from configuration_parameters where parameter_name = 'EnableNewCheckConstraintsByDefault';
 current_value | restart_value | database_value | default_value
---------------+---------------+----------------+---------------
 1             | 1             | 1              | 1
(1 row)
dbadmin=> CREATE TABLE chk_test3(ORDER_DATE timestamp, ITEM_CD int, QUANTITY int, PRICE int, CONSTRAINT chk_pos_quant CHECK (QUANTITY > 0));
CREATE TABLE
dbadmin=> insert into chk_test3(QUANTITY) values(-1);
ERROR 7230:  Check constraint 'public.chk_test3.chk_pos_quant' (chk_test3.QUANTITY > 0) violation: 'QUANTITY=-1'

dbadmin=> ALTER DATABASE testdb SET EnableNewCheckConstraintsByDefault=1;

ALTER DATABASE

dbadmin=> select current_value, restart_value,database_value, default_value from configuration_parameters where parameter_name = 'EnableNewCheckConstraintsByDefault';

current_value | restart_value | database_value | default_value

---------------+---------------+----------------+---------------

1 | 1 | 1 | 1

(1 row)

dbadmin=> CREATE TABLE chk_test3(ORDER_DATE timestamp, ITEM_CD int, QUANTITY int, PRICE int, CONSTRAINT chk_pos_quant CHECK (QUANTITY > 0));

CREATE TABLE

dbadmin=> insert into chk_test3(QUANTITY) values(-1);

ERROR 7230: Check constraint 'public.chk_test3.chk_pos_quant' (chk_test3.QUANTITY > 0) violation: 'QUANTITY=-1'

参考情報

主キー/一意キー制約による重複データの排除
http://vertica-tech.ashisuto.co.jp/primary-key/

参照整合性制約に違反したデータを確認する方法
http://vertica-tech.ashisuto.co.jp/pk_fk_chk/

検証バージョンについて

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

投稿タグ: constraint, 制約, チェック, check

MENU

はじめに

構文

制約の確認方法

実行例

参考情報

検証バージョンについて

カテゴリー

最近の投稿