PostgreSQL の DOMAIN を活用してみる
PostgreSQL で、限定された値しか入らないカラムに対して TEXT 型などあらゆる値を受け付ける型を設定してしまうことがあります。
アプリケーション側で適切にバリデーションできていれば問題は起こりませんが、より安全に、決められた値しか入れられないようにできたらよいのでは...？と思います。
そんなときに DOMAIN が使えると思います。
PostgreSQL のドキュメントの CREATE DOMAIN の説明より:
CREATE DOMAIN は新しいドメインを作成します。 ドメインとは本質的には、特別な制約（使用可能な値集合に対する制限）を持ったデータ型です。
ドメインを使用すると、共通な制約を 1 箇所に抽象化でき、メンテナンスに便利です。 たとえば、E-mail アドレスを格納する列が複数のテーブルで使用されていて、アドレス構文の検証のためすべてが同一の CHECK 制約を必要としているような場合です。
DOMAIN を使ってデータを限定する
区分値を指定するためにドメインを有効活用できそうです。
※弊社では区分のカラムに *_kbn という名前をつける風習があります。
DROP DOMAIN IF EXISTS test_status_kbn CASCADE;
CREATE DOMAIN test_status_kbn TEXT
CONSTRAINT check_test_status_kbn CHECK (
VALUE IN (
'10101' -- テスト状態区分(未テスト)
,'10102' -- テスト状態区分(テスト中)
,'10103' -- テスト状態区分(テスト済み)
)
);
- 既存のデータ型（上では
TEXT）をもとにドメインを作成できる -
CHECK制約をつけて満たすべき条件を指定できる
実行例:
# SELECT '10101'::test_status_kbn;
test_status_kbn
-----------------
10101
(1 row)
# SELECT '10105'::test_status_kbn; -- 区分値を間違えた
ERROR: value for domain test_status_kbn violates check constraint "check_test_status_kbn"
間違えた区分を CAST することができないので安全
テーブル定義:
上で作成したドメイン test_status_kbn のカラムを持つテーブルを作成できる。
CREATE TABLE tests (
uuid UUID NOT NULL DEFAULT gen_random_uuid()
,test_status_kbn test_status_kbn NOT NULL
,description TEXT NOT NULL DEFAULT ''
);
間違った区分値を INSERT しようとするとエラーが発生する。
# INSERT INTO tests (
test_status_kbn
,description
) VALUES (
'10105'
,'test2'
);
ERROR: value for domain test_status_kbn violates check constraint "check_test_status_kbn"
区分のカラムに空文字を入れてしまうなんてことも、もう起こらなくなる。
気になったこと
1. DROP DOMAIN ... CASCADE するとテーブルはどうなる...？
→ 該当のドメインを使っているカラムが DROP される
# SELECT * FROM tests LIMIT 1;
uuid | test_status_kbn | description | created_uuid | updated_uuid | deleted_uuid | created_at | updated_at | deleted_at | created_pg | updated_pg | deleted_pg | bk
--------------------------------------+-----------------+-----------------+--------------+--------------+--------------+-------------------------------+-------------------------------+------------+------------+------------+------------+----
49b42220-0dfb-42a9-8831-4ff138647f68 | 10103 | テスト中です。2 | | | | 2022-02-26 04:19:17.334207+00 | 2022-02-26 04:19:17.334207+00 | | | | |
(1 row)
# DROP DOMAIN test_status_kbn CASCADE;
NOTICE: drop cascades to 8 other objects
DETAIL: drop cascades to column test_status_kbn of table tests
drop cascades to column test_status_kbn of table garbage.tests
DROP DOMAIN
# SELECT * FROM tests LIMIT 1;
uuid | description | created_uuid | updated_uuid | deleted_uuid | created_at | updated_at | deleted_at | created_pg | updated_pg | deleted_pg | bk
--------------------------------------+-----------------+--------------+--------------+--------------+-------------------------------+-------------------------------+------------+------------+------------+------------+----
49b42220-0dfb-42a9-8831-4ff138647f68 | テスト中です。2 | | | | 2022-02-26 04:19:17.334207+00 | 2022-02-26 04:19:17.334207+00 | | | | |
(1 row)
2. 区分が追加されたらどうする？
→ DOMAIN から制約を外したあと、別の制約を付与し直す。
ALTER DOMAIN test_status_kbn DROP CONSTRAINT check_test_status_kbn;
ALTER DOMAIN test_status_kbn ADD CONSTRAINT check_test_status_kbn CHECK (
VALUE IN (
'10101' -- テスト状態区分(未テスト)
,'10102' -- テスト状態区分(テスト中)
,'10103' -- テスト状態区分(テスト済み)
,'10104' -- テスト状態区分(不明)
)
);
3. 追加しようとする制約を満たさないデータがすでにテーブルに存在した場合、ADD CONSTRAINT しようとするとどうなる？
→ 制約を満たさないテーブルがある、というエラーが出てくれる
# ALTER DOMAIN test_status_kbn DROP CONSTRAINT check_test_status_kbn;
INSERT INTO tests (
test_status_kbn
,description
) VALUES (
'10105' -- 存在しない区分
,'invalid'
)
;
# ALTER DOMAIN test_status_kbn ADD CONSTRAINT check_test_status_kbn CHECK (
VALUE IN (
'10101' -- テスト状態区分(未テスト)
,'10102' -- テスト状態区分(テスト中)
,'10103' -- テスト状態区分(テスト済み)
,'10104' -- テスト状態区分(不明)
)
);
ERROR: column "test_status_kbn" of table "tests" contains values that violate the new constraint
4. ドメインに NOT NULL をつけられるか？
できる。でも、DOMAIN は NULL を許容するようにしておいて、必要に応じてテーブルに NOT NULL 制約をつけるようにするのがよいらしい（参考: ドキュメント）