2014年10月24日金曜日

UNIQUE制約でのNULLの扱い

0 コメント
PostgreSQLで、テーブルの複数列でUNIQUE制約をかけたとき、その中にNULLを許可する列があった場合の挙動について。

次のテーブルを作成します。
このテーブルに次のINSERT文でデータを入れてみます。
結果は次のようになります。
id=3は、UNIQUE制約違反でINSERTに失敗しています。
ここで引っかかったのはid=5がUNIQUE制約違反にならず、INSERTに成功しているところです。

私のイメージだと、NULLは何もないという感覚だったので、id=4とid=5のデータはUNIQUE制約に違反してid=5はINSERTに失敗すると思っていました。
調べてみると、PostgreSQLの一意制約の解説では、
一般に、制約の対象となる列について同じ値を持つ行が、テーブル内に1行を上回る場合は、一意性制約違反になります。 しかし、この比較では2つのNULL値は等価とはみなされません。 つまり、一意性制約があったとしても、制約対象の列の少なくとも1つにNULL値を持つ行を複数格納することができるということです。 この振舞いは標準SQLに準拠していますが、この規則に従わないSQLデータベースがあることを聞いたことがあります。 ですから、移植する予定のアプリケーションを開発する際には注意してください。
とあり、NULLは等価ではないとあり、上の例ではid=4とid=5はUNIQUE制約に違反せず、INSERTできるということになります。
文中の
この規則に従わないSQLデータベースがあることを聞いたことがあります。
は、MicrosoftのSQL Serverが該当するみたいです。
OracleやMySQLなどはPostgreSQLと同じ挙動をするんだとか。
移植の際などには気をつける必要があります。

対策としては、std列をNOT NULLとして、id=4,id=5には''を入れるようにすれば、意図した動きになります。

では、ADO.NETのDataTableはどうなんでしょう?
こんなサンプルを作って試してみました。 結果はこうなりました。 PostgreSQLとは異なり、NULLを同じ値として扱っているようです。
PostgreSQLにNULLを許可した列を含むUNIQUE制約を持つテーブルからADO.NETのDataTableにデータを取り込んだ場合、エラーになる可能性が考えられます。
UNIQUE制約をかける列は、NOT NULLにするべきでしょうね。