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=>
