PostgreSQLで複雑な条件のCHECK制約を実装する

はじめに

アンケートの単一回答のような「ある集合の中で一つだけを選択できる」機能を実装していました。
この制約をDBのテーブルでも表現できるといいなと思いました。

最終的には 関数+CHECK制約 で実現できました。

環境

DB: PostgreSQL 12

テーブルを作る

まずはテーブルを作りましょう。
ER図にすると以下のようになります。

-- 設問 「映画は好きですか？」 など
CREATE TABLE questions (
 question_id INT NOT NULL,
 question NCHAR VARYING(100) NOT NULL
);

ALTER TABLE questions ADD CONSTRAINT PK_questions PRIMARY KEY (question_id);

-- 回答の選択肢 「当てはまらない」「やや当てはまる」 など
CREATE TABLE answer_levels (
 answer_level_id INT NOT NULL,
 answer_level_title NCHAR VARYING(10) NOT NULL,
 answer_level_score INT NOT NULL
);

ALTER TABLE answer_levels ADD CONSTRAINT PK_answer_levels PRIMARY KEY (answer_level_id);

-- 回答
CREATE TABLE responses (
 question_id INT NOT NULL,
 answer_level_id INT NOT NULL,
 response_type INT NOT NULL CHECK(response_type < 2) -- 0: 未選択 1: 選択
);

ALTER TABLE responses ADD CONSTRAINT PK_responses PRIMARY KEY (question_id,answer_level_id);


ALTER TABLE responses ADD CONSTRAINT FK_responses_0 FOREIGN KEY (question_id) REFERENCES questions (question_id);
ALTER TABLE responses ADD CONSTRAINT FK_responses_1 FOREIGN KEY (answer_level_id) REFERENCES answer_levels (answer_level_id);

テストデータを作る

次にテストデータを作ります。
コメントにもある通り、このテストデータは期待しないデータです。
ですが、この段階で実行するとできてしまいます。

insert into
  questions(question_id, question)
values
  (1, 'q-a')
, (2, 'q-b')
, (3, 'q-c')
;

insert into
  answer_levels(answer_level_id, answer_level_title, answer_level_score)
values
  (1, 'answer-a', 20)
, (2, 'answer-b', 40)
, (3, 'answer-c', 60)
, (4, 'answer-d', 80)
, (5, 'answer-e', 100)
;

insert into
  responses(question_id, answer_level_id, response_type)
values
  (1, 1, 0)
, (1, 2, 0)
, (1, 3, 0)
, (1, 4, 1)
, (1, 5, 0)
, (2, 1, 0)
, (2, 2, 0)
, (2, 3, 0)
, (2, 4, 0)
, (2, 5, 0)
, (3, 1, 0)
, (3, 2, 1)
, (3, 3, 0)
, (3, 4, 1) -- question_id=3かつresponse_type=1の回答はすでにあるので、insertできないようにしたい
, (3, 5, 0)
;

CHECK制約をつける

さて困りました。
設問に対して単一回答のはずが、複数の回答を登録できてしまいます。

そこでresponsesテーブルに対してCHECK制約をつけようと思います。
「設問IDを指定して、responses.response_type=1のレコード数を取得する」関数を作って
CHECK制約の条件として利用するようにします。

Postgre SQLでは PL/pgSQL という言語を使って関数を実装できます。
以下のSQLを実行します。

-- 指定したquestion_idの中で、response_type=1のレコード数を返す関数
CREATE FUNCTION GetResponsedRowCount(
  questionId int
) RETURNS int AS $$
DECLARE responsedRowCount int;
BEGIN
  SELECT COUNT(*) INTO responsedRowCount
  FROM responses
  WHERE
    question_id = questionId
    and response_type = 1
  ;
  RETURN responsedRowCount;
END;
$$ LANGUAGE plpgsql;

-- insert/updateする前の時点でresponse_type=1のレコードがなければOK
-- response_type=0の場合は常にOK
ALTER TABLE responses ADD CONSTRAINT ResponsedRowCountOnlyOne CHECK (GetResponsedRowCount(question_id) = 0 or response_type = 0);

CHECK制約は insert/update の実行前にチェックされる

はじめはinsert/update後の状態を定義するものと思っていたので
CHECK制約を以下のように設定していましたが、期待通りには動作しませんでした。

ALTER TABLE responses ADD CONSTRAINT ResponsedRowCountOnlyOne CHECK (GetResponsedRowCount(question_id) < 2);

ドキュメントには以下の記述があったのでCHECK制約の条件を変更しました。

CHECK句は、論理型の結果を生成する、新しい行または更新される行が挿入または更新処理を成功させるために満足しなければならない式を指定します。

まとめ

以上で条件付きCHECK制約を実装できました。
上であげたテストデータのSQLを実行すると、CHECK制約例外が発生します。

他の方法があればぜひ知りたいです！

ERROR: new row for relation "responses" violates check constraint "responsedrowcountonlyone"
    詳細: Failing row contains (3, 4, 1).

参考文献

https://www.postgresql.jp/document/10/html/sql-createtable.html
https://stackoverflow.com/questions/866061/conditional-unique-constraint
https://qiita.com/SRsawaguchi/items/411801e254ee66f511f1#plpgsql%E3%81%AB%E3%82%88%E3%82%8B%E9%96%A2%E6%95%B0