DB設計におけるunique制約
ポートフォリオ作成にあたって、各エンティティやカラムの抽出を行っていきました(DBMLへの書き起こし)。その中でunique制約について触れる事がありましたが、疑問に思うことがいくつかあったので本記事にまとめます。
そもそもunique制約とは？
unique制約は「DB上において、同じ値を複数行に含めない」という制約です。
例えば、ユーザー情報テーブルでのemailカラムは、重複防止のためにunique制約をつけることで、一意性をもたせることができます。
複合的・部分的unique制約
以下のような場合、unique制約はどうなるでしょうか。
・「my_set_id」に「A」テーブル、「B」テーブルそれぞれから「item_id」を追加する。(両方NULLはNG)
・1つのテーブルにおいて、追加するアイテムの重複のみ禁止する。
ただ単に各テーブルの「item_id」カラムに対してuniqueをつけると、全マイセットまたいで、Aのアイテム重複が禁止されてしまいます。
→「my_set_id」とAの「item_id」を組み合わせ、全体で一意性をもたせる複合unique制約で回避可能です。
UNIQUE(my_set_id, item_id)
また、NULLが含まれる場合、同じ値でも別行として許可されるPostgreSQLの仕様もあるみたいです。
つまり「A」「B」それぞれがNULLの場合を制約から除外しなければなりません。
→複合unique制約に条件を足して、一部の行のみ制御対象とする方法を部分的unique制約といいます。
UNIQUE(my_set_id, item_id) WHERE item_id IS NOT NULL
UNIQUE(my_set_id, custom_item_id) WHERE custom_item_id IS NOT NULL
部分的uniqueはDBの依存性が強く、古いバージョンのMySQLなどは直接制約を使用することができないようです。
このように、カラムの重複性によって適切なunique制約を割り当てる必要があることがわかりました。
最後に
かなりコンパクトになってしまいましたが、基礎的な部分をまとめてみました。他にも大規模開発向けの「部分的unique index」なるものがあるようですが、これはまた別の記事でまとめようと思います。