はじめに
DB設計をしていて気づいた点があったのでまとめます。
前提
MySQL version 5.6
複合主キー制約
主キー制約を複数のカラムに設定することを複合主キー制約といいます。
対象の各カラムには重複した値を入れることができますが、各カラムの値の組み合わせが同一のレコードを追加することが出来なくなります。
また、複合主キーを設定した各カラムには自動的にNOT NULL制約が設定されます。
| item_id ( PK ) | name ( PK ) | price | レコードへの登録可否 |
|---|---|---|---|
| 1 | ボールペン | 100 | OK |
| 1 | 万年筆 | 2000 | OK |
| 2 | ボールペン | 300 | OK |
| 150 | NG（item_idとnameが重複している） | ||
| 万年筆 | 1200 | NG（item_idがnull） | |
| 2 | 500 | NG（nameがnull） | |
| 350 | NG（item_idとnameがnull） |
複合ユニーク制約
ユニーク制約を複数のカラムに設定することを複合ユニーク制約といいます。
複合主キー制約と同様、対象の各カラムには重複した値を入れることができますが、各カラムの値の組み合わせが同一のレコードを追加することが出来なくなります。
複合主キー制約との違いとしては、複合ユニークを設定したカラムでは自動でNOT NULL制約が設定されないことです。また、1つのテーブルで複数の複合ユニーク制約を持つことができます。
| item_id ( UK, NOT NULL ) | name ( UK ) | price | レコードへの登録可否 |
|---|---|---|---|
| 1 | ボールペン | 100 | OK |
| 1 | 万年筆 | 2000 | OK |
| 2 | ボールペン | 300 | OK |
| 150 | NG（item_idとnameが重複している） | ||
| 万年筆 | 1200 | NG（item_idがnull） | |
| 2 | null | 500 | OK |
| null | 350 | NG（item_idがnull） |
複合ユニーク制約の懸念点
1つのテーブル内に複数の制約を設定できることから、一見便利に見える複合ユニーク制約ですが、懸念点もあります。デフォルトでNOT NULL制約が設定されないので、対象カラムにnull許容のカラムを含むことを許してしまい、その場合以下のような重複レコードが発生します。
| item_id ( UK, NOT NULL ) | name ( UK ) | price | レコードへの登録可否 |
|---|---|---|---|
| 1 | null | 500 | OK |
| 1 | null | 350 | OK |
上記事象の発生事由として、MySQLが「すべてのnullをユニークとして解釈している」ことが挙げられます。つまり、MySQLでは上記2つのレコードは別物として扱われます。
設計者としては、こうした重複レコードが発生するのは想定外であると思われるので、複合ユニーク制約に設定するカラムはnullが入らない前提で設計する必要があるといえます。
※ただし文字列型であれば、insert/update時にnullではなく''を指定することで重複レコードを回避できるようです。（そもそもnullを入れる前提で複合ユニーク制約を使うことってあるんですかね…？）
まとめ
複合主キー制約と複合ユニーク制約の違いをまとめます。
-
複合主キー制約
・ 設定対象のカラムに対し、自動でNOT NULL制約が設定される。
・ 1つのテーブルに対して1つの複合主キー制約のみ設定できる。 -
複合ユニーク制約
・ 設定対象のカラムに対し、自動でNOT NULL制約が設定されない。
・ 1つのテーブルに対して複数の複合ユニーク制約を設定できる。
・ 設定対象のカラムにnullが入る場合、重複レコードが発生する可能性がある。
正直、明確な使い分けについてはまだきちんと理解できていなかったり。。
（1つのテーブルに複数の複合ユニーク制約を設定すること自体レアケースだと思うので、複合ユニーク制約の長所を活かせる場面があまり思い浮かばないです）
後日また調査することがありましたら、追記します。
参考
SQLの制約の種類とその指定方法
https://qiita.com/Uejun/items/de02b78adbc54868d40d