読者です 読者をやめる 読者になる 読者になる

mokky14's IT diary

IT関係の仕事メモ、勉強会の感想など書いてます。

PostgreSQLでテーブルの列にCHECK制約を付与

PostgreSQL

PostgreSQLでカラムの値制限を行う方法について確認したのでメモ。

環境
OS: CentOS 6.4
PostgreSQL: 8.4.13

まずはテーブルをCREATEするときに制約を付けてみる。

$ cat tmp.sql
CREATE TABLE PROP (
 ID VARCHAR(64) NOT NULL,
 VAL INT NOT NULL CHECK (VAL > 0 AND VAL < 1000),
 VAL2 INT ,
 STR VARCHAR(32) ,
 STR2 VARCHAR(32) 
);
ALTER TABLE PROP ADD CONSTRAINT PK_PP PRIMARY KEY (ID);


mokky_tmp=> \i tmp.sql
DROP TABLE
CREATE TABLE
psql:tmp.sql:11: NOTICE:  ALTER TABLE / ADD PRIMARY KEYはテーブル"prop"に暗黙的なインデックス"pk_pp"を作成します
ALTER TABLE
mokky_tmp=> \d prop
          テーブル "public.prop"
 カラム |          型           |  修飾語  
--------+-----------------------+----------
 id     | character varying(64) | not null
 val    | integer               | not null
 val2   | integer               | 
 str    | character varying(32) | 
 str2   | character varying(32) | 
インデックス:
    "pk_pp" PRIMARY KEY, btree (id)
CHECK 制約:
    "prop_val_check" CHECK (val > 0 AND val < 1000)

試しにデータ登録してみる。

mokky_tmp=> insert into prop values(1,1,null,null,null);
INSERT 0 1
mokky_tmp=> insert into prop values(1,0,null,null,null);
ERROR:  リレーション"prop"の新しい行は検査制約"prop_val_check"に違反しています
mokky_tmp=> insert into prop values(1,999,null,null,null);
ERROR:  重複キーは一意性制約"pk_pp"に違反しています
mokky_tmp=> insert into prop values(2,999,null,null,null);
INSERT 0 1
mokky_tmp=> insert into prop values(3,1000,null,null,null);
ERROR:  リレーション"prop"の新しい行は検査制約"prop_val_check"に違反しています

制約に引っかかるデータは登録できない。

後から制約を追加することもできる。
一つの列に複数の制約を付与することも出来るし、テーブルの他列と比較する制約も定義できる。

mokky_tmp=> alter table prop add constraint prop_val2_check check (val2>=0 and val2<=10000);
ALTER TABLE
mokky_tmp=> alter table prop add constraint prop_val2_check2 check (val2>val);
ALTER TABLE
mokky_tmp=> \d prop
          テーブル "public.prop"
 カラム |          型           |  修飾語  
--------+-----------------------+----------
 id     | character varying(64) | not null
 val    | integer               | not null
 val2   | integer               | 
 str    | character varying(32) | 
 str2   | character varying(32) | 
インデックス:
    "pk_pp" PRIMARY KEY, btree (id)
CHECK 制約:
    "prop_val2_check" CHECK (val2 >= 0 AND val2 <= 10000)
    "prop_val2_check2" CHECK (val2 > val)
    "prop_val_check" CHECK (val > 0 AND val < 1000)

mokky_tmp=> select * from prop;
 id | val | val2 | str | str2 
----+-----+------+-----+------
 1  |   1 |      |     | 
 2  | 999 |      |     | 
(2 行)

mokky_tmp=> update prop set val2=1 where id='1';
ERROR:  リレーション"prop"の新しい行は検査制約"prop_val2_check2"に違反しています
mokky_tmp=> update prop set val2=1 where id="1";
ERROR:  列"1"は存在しません
行 1: update prop set val2=1 where id="1";
                                      ^
mokky_tmp=> 
mokky_tmp=> update prop set val2=1 where id='1';
ERROR:  リレーション"prop"の新しい行は検査制約"prop_val2_check2"に違反しています
mokky_tmp=> update prop set val2=2 where id='1';
UPDATE 1
mokky_tmp=> update prop set val2=10001 where id='2';
ERROR:  リレーション"prop"の新しい行は検査制約"prop_val2_check"に違反しています
mokky_tmp=> update prop set val2=10000 where id='2';
UPDATE 1
mokky_tmp=> 

正規表現を使用することで、使用可能文字を制限する制約も定義できる。
以下はvarchar型変数の使用可能文字を半角英数に限定する制約。
正規表現の先頭と最後の'^'と'$'を忘れないようにすること。(しばらくハマった)

mokky_tmp=> alter table prop add constraint prop_str_check check (str ~ '^[a-zA-Z0-9]+$');
ALTER TABLE
mokky_tmp=> \d prop
          テーブル "public.prop"
 カラム |          型           |  修飾語  
--------+-----------------------+----------
 id     | character varying(64) | not null
 val    | integer               | not null
 val2   | integer               | 
 str    | character varying(32) | 
 str2   | character varying(32) | 
インデックス:
    "pk_pp" PRIMARY KEY, btree (id)
CHECK 制約:
    "prop_str_check" CHECK (str::text ~ '^[a-zA-Z0-9]+$'::text)
    "prop_val2_check" CHECK (val2 >= 0 AND val2 <= 10000)
    "prop_val2_check2" CHECK (val2 > val)
    "prop_val_check" CHECK (val > 0 AND val < 1000)

mokky_tmp=> update prop set str='abcedfxyzABCDXYZ1234567890-' where id='1';
ERROR:  リレーション"prop"の新しい行は検査制約"prop_str_check"に違反しています
mokky_tmp=> update prop set str='abcedfxyzABCDXYZ1234567890' where id='1';
UPDATE 1
mokky_tmp=> select * from prop;
 id | val | val2  |            str             | str2 
----+-----+-------+----------------------------+------
 2  | 999 | 10000 |                            | 
 1  |   1 |     2 | abcedfxyzABCDXYZ1234567890 | 
(2 行)

mokky_tmp=> 

ちなみに、後から制約を追加する場合は、制約違反になるデータが登録されているとエラーになる。

mokky_tmp=> select * from prop;
 id | val | val2 |            str             | str2 
----+-----+------+----------------------------+------
 1  |   1 |    2 | abcedfxyzABCDXYZ1234567890 | 
 2  | 999 |      |                            | 
(2 行)

mokky_tmp=> alter table prop add constraint dmy_chk check (str ~ '^A');
ERROR:  一部の行で検査制約"dmy_chk"に違反しています
mokky_tmp=>