質問: 有効フラグ、削除フラグなどのフラグ項目に NOT NULL 制約を付与しないDB設計をよく見かけます。「仕様上は 0 と NULL は同じ意味で扱ってください」と指示されるのですが、NOT NULL 制約(とデフォルト値)を付与することに何らかのデメリットがあるのでしょうか。 フラグ項目に限らず一般的に、 NULL を格納する必要のない項目に NOT NULL 制約を付与することで生じるデメリットは何かあるでしょうか。
回答: 開発者の経験上、**NULL を格納する必要のない項目には NOT NULL 制約を付与する方がメリットが多い**と感じています。 ご質問はSQLアンチパターンで言う所の「フィア・オブ・ジ・アンノウン(恐怖のunknown)」に通じる内容ですね。 (手元に資料がないのでうろ覚えですが)フィア・オブ・ジ・アンノウンでは、NULLが必要な項目にNOT NULL制約を入れるアンチパターンについて言及していたはずです。 例えば`男性=1` `女性=2`のカラムに`未入力=unknown`を設定していたところ、ある日入力欄に「不詳」が追加されて`不詳=unknown` `未入力=null`にする設計変更が入ったため、本番環境のデータ更新を余儀なくされるケースなど、ひと手間かかるアンチパターンです。 別のアンチパターンとして、NOT NULL制約が必要なカラムに制約を設けていないことも挙げられます。 有効フラグが立っていないレコードを検索する際に、NULLと'0'が混在している場合は`ENABLE_FLG NOT in ('1')`で正しい結果が取得できないアンチパターンもあります。 上記資料の結論は、NULLの特性を理解して適切に使いなさい、必要ならNOT NULL制約でアンチパターンを回避しなさいというものだったと記憶しています。 なおNOT NULL 制約を付与するデメリットについては、若干insertなどの操作が遅くなることを主張する人もいるかもしれません。 検証記事によると、Oracleで100万件のデータをinsertした場合に約0.6%速度が低下するようです。 現実の運用では無視できる速度差でしょう。 * NOT NULL制約なし - 172.411秒 * NOT NULL制約あり - 173.474秒 フラグ項目にNOT NULL 制約を付与しない設計をする主な理由は下記の例が多く感じます。 * フレームワークの共通ファンクションでinsertすれば必ずフラグを登録するから制約は速度低下にしかならない * 有効フラグは`ENABLE_FLG = '1'`で検索するだけだから、それ以外は何でも良い + 正常系の処理では論理削除されたものを検索しないので事故にならないという発想 * テストデータ作る時に制約は邪魔だから消したい + テストデータを論理削除するときに削除フラグをnullでupdateして放置という恐ろしいテスターも... * 単純に制約付け忘れ / 制約?なにそれおいしいの? いずれにせよ必要な制約を付与しないことは、テーブル設計の意図がテーブルの実体に反映されていない状態であり、作曲家がフォルテピアノなどの強弱記号を楽譜に書いていない状態に似ています。 開発者や演奏者が好き勝手に解釈をする環境よりも、制約によって不当な処理はできない環境の方が健全ではないでしょうか。 ※ 新人やインフラチームが「良く分からないから制約消しました!」という悲劇は防げませんが、これは情報共有や体制の話なので回答には含めません