PostgresとMySQLでの複合ユニーク制約でのNULLの扱い
ユニーク制約ではNULLは同じ値とみなされないので、複合ユニーク制約で、1個でもNULLになるカラムがあれば、書き込めてしまいます。MySQLでは知ってたんですが、そういえばPostgresだとどうなんだろうと思って、調べてみました。
結論
結論としては、標準化されたSQL規格でそう決まってるらしいので、MySQLでもPostgresでも同様に、複合ユニーク制約でNULLを含むと書き込めます。標準SQL規格を直接確認してないんですけども。標準SQL規格ってネットに公開されてたりしないのかな。探したけど見つからなかった。
一般に、制約の対象となる列について同じ値を持つ行が、テーブル内に１行を上回る場合は、一意性制約違反になります。 しかし、この比較では2つのNULL値は等価とはみなされません。 つまり、一意性制約があったとしても、制約対象の列の少なくとも1つにNULL値を持つ行を複数格納することができるということです。 この振舞いは標準SQLに準拠していますが、この規則に従わないSQLデータベースがあることを聞いたことがあります。 ですから、移植する予定のアプリケーションを開発する際には注意してください。
http://www.postgresql.jp/document/pg904doc/html/ddl-constraints.html#AEN2445
MySQLについては「複合 ユニーク NULL」とかで検索すると結構エントリーが見当たるけど、Postgresはなかなか出てきませんでした。その代わり、公式のドキュメントに書いてあるのを見つけました。逆にMySQLのドキュメントでは見当たらなかった・・・
値がNULLのフィールドはUNIQUE制約の対象にならない。複数レコードにNULLがあっても制約違反にはならない。
データベース PostgreSQL 制約 - s-kita’s blog
1ヶ所だけ、Postgresでの複合ユニーク制約でのNULLについて、触れてるエントリーも見つけました。
論理削除
この仕様、論理削除と相性が良くないですね。deleted_atというカラムに、削除した日時があれば論理削除済み、NULLだったら未削除という事をやると、ユニーク制約が出来ません。どうするのが良いんだろう。
余談
MySQLのドキュメントって、なんか読みにくいね。ユニーク制約について書いてあるページを、結局目次からは見つけられなかった。
