🙂

SQLアンチパターン感想その二ーENUM型を扱う

2022/07/03に公開

SQL

tech

今回はSQLアンチパターンの感想その二です。

以前のプロジェクトで何回か出会ったことがあり、一つのカラムに対して、いくつかの選択肢がある、という場合、ENUMタイプを安易に使ってしまいましたが、この本を読んだら改めて反省しました。

解決したい問題とは

例えばブログポストのステータスというフィールドがあるとして、そのステータスには、DRAFT, PUBLISHEDといった値があります。ただ、ここはstatusフィールドに対して、DRAFTとPUBLISHEDのみに限定したいです。draftでも、newでも、挿入・更新できないように制限をかけたいところです。

アンチパターン：`CHECK`もしくは`ENUM`を用いる

CREATE TABLE Post ( 
  -- ...
  status VARCHAR(20) CHECK (status IN ('DRAFT', 'PUBLISHED')) 
);

varchar型とはいえ、CHECKを用いることで、確かに挿入・更新時に選択肢を制限することが可能です。

もしくは、冒頭で触れましたが、ENUMタイプでカラムを作ることも考えられます。

CREATE TABLE Post ( 
  -- other columns 
  status ENUM('DRAFT', 'PUBLISHED')
);

個人的に後者の方をよく使っていましたが、というのは運用してからの候補変更といった要求が少なかった関係か、あまり何の問題があるかは意識していませんでした。

問題点

全部候補の取得が面倒

例えば、フロントエンドで候補をセレクトインプットの形で作りたい時に、バックエンドから候補のリストを取得するケースが想定できます。しかし、ENUMにしても、CHECKにしても、候補リストの取得がややトリッキーなことになります。

SELECT column_type 
FROM information_schema.columns 
WHERE table_schema = 'xxx_schema' 
AND table_name = 'post' 
AND column_name = 'status';

さらに、仮にこのようにENUMの候補を取得できたとしても、返り値は望む通りの配列でない可能性も高いです。例えばMySQLの場合だと、上記のSQLを実行して、ENUM('DRAFT', 'PUBLISHED', 'ARCHIVED')のような文字列が結果となります。するとこの文字列の処理も当然別途必要になります。

Checkはもっとやりにくいので、もしDB以外のところで候補リストが必要であれば、もう定数とかに保存するしかないかもしれません。となると、DB側で変更する時に、プログラムの方の変更もしなければならないし、忘れてしまうと登録ができなくなるなどのリスクもあります。

候補変更時のコストが高い

変更の話がありましたが、ENUMとかcheck制約とかに、候補の変更が容易ではありません。基本的にフィールド自体の定義をし直さないといけません。

新規追加の時に以前の候補リスト＋新しい候補を持って、ALTER分でフィールドを再定義。ただこれだと卵が先か鳥が先かの問題になり、先ほどの候補リスト取得の問題とつながります。仮に他のドキュメントや設計書を参考にして追加できるとしても、運用中のためサービスを一時ダウンすることができるかどうかも問題になります。

変更のケースはもっとややこしいです。例えば、DRAFTという候補を、DRAFTEDに変更したい場合、既存のポストには既にDRAFTのレコードがあるので、変更するときにinvalidな値になり失敗してしまいます。

削除も似たような問題があります。既存のレコードに使われているなら安易に削除できません。先に新しい候補を追加、既存レコードのステータス変更、フィールドの再定義など、煩雑な操作が必要になります。

他のDBへマイグレートする時の互換性

checkとenumの仕組みなどに関わりますが、RDB種類によって同じ書き方で対応不可な可能性が存在し、互換性の問題もあります。この辺りはまだ詳しく調べていませんが、軽く念頭に置いておきます。

解決案：「関係」を思い出す

簡単にいえば、選択肢を一つのテーブルで管理し、外部キーの形で参照すれば良いと。

CREATE TABLE PostStatus ( 
  -- other columns 
  status VARCHAR(20) PRIMARY KEY
);
INSERT INTO PostStatus (status) VALUES ('DRAFT'), ('PUBLISHED'), ('ARCHIVED');

CREATE TABLE Post ( 
  -- other columns 
  status FOREIGN KEY (status) REFERENCES PostStatus (status) ON UPDATE CASCADE
);

そうすると、候補リストの取得、新規追加、更新、削除が非常に簡単になり、整合性も外部キーによって保ちます。また、外部キー制約はRDBの種類によらず共通するものなので、互換性の心配もありません。

これこそ、関係型DBの関係ではないかと。

アンチパターンを利用するケース

誤解に招くかもしれませんが、今までの話というのは、checkとenumは使ってはいけないということはなく、単純に適している場面が違うのではないかとのことです。

ENUM型は、候補が変更することがない場合、例えばON/OFF, LEFT/RIGHTなどのような対になる値に使うことができます。全部候補の取得は確かにメタデータから取るしかないですが、他の副作用がほぼありません。

CHECKは候補制限よりも幅広い使い道があります。例えば商品の価格はポジティブでないといけない時に、price >= 0 とかのチェック制約を追加できます。また、イベントの開始時間と終了時間のフィールドがあるとして、終了時間は常に開始時間より大きいのをチェックするなど、フィールド間データの整合性を保つために非常に役立ちます。

終わりに

この問題について、enumで良いケースと、テーブルで管理した方が良いケースがあると思います。enumは本来こういう場面のために作られたかと思いますが、ネーティブなデータタイプではなく、実際は整数（インデックス）と文字列のマッピングの形でDBに管理されています（保存値は数字）。そういう意味で言えば、enumタイプ自身は元より「関係」を意識しているような設計になっているでしょう。

それに比べて、check制約はこの問題に使うのは、多少大器小用な感じはします。目的は達成できるものの、やはり候補の取得、変更などに同じ問題があるため、あまりおすすめとはいえないですね。

次にENUMのフィールドと出会う時に、変更する可能性と頻度を考慮した上で、テーブルとして管理するか、ENUM型のままで良いかを判断すると良いでしょう。

ではでは。