今北産業まとめ

チェック制約ではSELECTが書けない
代わりにストアドファンクションを使う
最終手段なのでメインで多用するのはやめよう

やりたいこと

　チェック制約でカラムの値をチェックしてバリデーションしたいことがある。同じINSERTの値であればCASEで下記のような方法で対応することができる。

soudai.hatenablog.com

　しかしチェック制約ではSELECTによって事前のデータを確認することができない。正しくRDBで対応しようと思うと正規化していけば実はできるのだけど、レコード数が増える。仕組みが複雑になるなどの理由で嬉しくない場合も多い。例えば中学校の時間割とかがそうで、先生、教科の組み合わせと時間割を重複しないように管理する必要*1がある。

　設計したことがない人はぜひ、設計してみてほしいのだけど、単純に先生テーブル、教科テーブル、時間割テーブルくらいで実装すると時間割は簡単に矛盾が起こり得る。だからアプリケーション側でループを回したり、他のデータをSELECTしたりする必要がある。それをテーブル設計だけで防ぐためには正しく設計力が必要だ*2。

　これがいい例だったのだけど時間割の中で重複してないことをチェック制約で確認できればテーブルはシンプルになる*3。ということをやりたいときは次のようになる。

ストアドファンクションを使う

　冒頭でも書いたが、チェック制約でSELECTを使いたいときはストアドファンクションを使い、ストアドファンクションをチェック制約に設定する。

　時間割は複雑になるのでもっとシンプルなケースを紹介する。

userテーブルは権限を設定する必要がある
roleテーブルには削除フラグ*4がある
削除フラグが1の権限は設定してはならない

CREATE FUNCTION get_active_role(
   role_id bigint,
) RETURNS int AS
$$
DECLARE
   responsed_row_count int;
BEGIN
-- ここに検索したいSELECTを書く
   SELECT COUNT('*') INTO responsed_row_count
FROM role
   WHERE id = role_id
      AND delete_flag = 0
   RETURN responsed_row_count;
END;
$$ LANGUAGE plpgsql;

-- ストアドファンクションをチェック制約に設定する
ALTER TABLE user
   ADD CONSTRAINT check_room
   CHECK (get_active_role(role_id) = 1);

　このチェック制約によって、userテーブルに登録されるデータの権限は必ずactiveな権限になる。これによって次のように毎回role_idをチェックする必要がなくなる。

　また今回はPL/SQLで書いているが、ご存知の通り、PostgreSQLはストアドファンクションをPL/JavaScriptやPL/Python *5でも書ける。

SELECT * FROM user 
  INNER JOIN role
     ON role.id = user.role_id
     AND role.delete_flag = 0

チェック制約は万能ではない

　このパターンではuserにデータを登録したあと、既存のroleの削除フラグを更新した場合は無効なデータが生まれる。そのためroleにもチェック制約が必要だし、そもそも削除フラグを採用することはとても難しい設計になるということがわかる。削除フラグを使っている場合はチェック制約ではなく、テーブルに状態を持たせないようにするべきだ。

　このようにチェック制約とストアドファンクションに安易に頼るのではなく、飛び道具としてチェック制約を使うと正規化を一段減らせるケースがあるということを覚えておこう。最後の手段として覚えておくとリファクタリングのときやどうしても難しいロジックのときにデータを守ってくれる。

　なおMySQLも 8.0.16 からチェック制約が使えるが、同じことができるかは検証してない。 ※2021/07/12 10:16 追記

f:id:Soudai:20210712101717p:plain