「NULLがUNIQUE制約に縛られないことを利用する」のは、正当なNULLの使い方
リンク先は「UNIQUE INDEXを振った列に複数のNULLを投入できること利用して、ユニークであるべきユーザIDの使い回し(=退会したユーザのIDを新規ユーザに開放する)を実現する」という話。
アクティブなユーザ名はユニークにしたいけど削除されたユーザの情報は残したい。でも削除済みユーザテーブルは作りたくない
とかいうワガママを発揮したい時にdeleteフラグに使えないかなーなんてだめですかそうですか。なんか他にまともな方法無いですか…。
いやーこれはまともな方法じゃないでしょうか。
「NULLはNULLに一致しない」のが絶対の原則なのだから、NULLを使ってUNIQUE制約を回避するのは裏技でもwork-aroundでもない、正当なテクニックでしょう。
私はTM派なので実表上にnullを発生させる設計はしないが、Nullableな列を使ってもよい流派の方であれば、特に避ける理由はないんじゃなかろうか。
「NULLがUNIQUE制約に縛られない」ことに違和感がある方は、NULLについて「対象にはその値があるが、システムはその値を知らない状態」ではなく、単に「値が無い状態」をイメージしているのではないかと推測する。後者のイメージを持っていると
- NULL+1がNULLになる
- 1=NULLがUnknownになる
- UNIQUE制約のある列に複数のNULLを設定できる
といったSQLの挙動について、なぜそうなるのか理解できないはずだ。「値が無い」なら、
- NULL+1は1でいいんじゃないか... 実際SUM関数で集約すれば1になるのだから...
- 1=NULLはFalseでいいんじゃないか...「値が無い状態」が「1という値」に一致するわけがないのだから...
みたいな疑念が沸くだろうから。
NULLは「値がある(が、システムはその値を知らない)状態」だと思えば、これらの疑問は解消する。
「NULLがUNIQUE制約にひっかかるDBMS」は矛盾しているのか？
リンク先のブックマークコメントによると、SQL Server等、複数のNULLがUNIQUE制約にひっかかる実装も存在するらしい。
そういうDBMSは「NULLはNULLに一致する」「NULLはNULLに一致しない」を同時に言っていることにならないか。つまり矛盾しているのではないか。大丈夫か。
... と思ってSQL標準の解説書を読んでみたら、SQLでは「一致」と「重複」が別の概念で、NULLについては「重複するが一致しない」のだとわかった。
UNIQUE制約を一致の概念に基づいて動作させるのがSQL標準で、重複の概念に基づいて動作させているのがSQL Server等ということで、後者にも矛盾はないようです。
