bouzuya.hatenablog.com

ぼうずやのにっき

UNIQUE 制約は NULL の列を複数含んでも良い

MySQLPostgreSQL の UNIQUE 制約は NULL の列を複数含んでも良い。

MySQL 8.0 https://dev.mysql.com/doc/refman/8.0/ja/create-table.html

すべてのエンジンについて、UNIQUE インデックスは、NULL を含むことができるカラムでの複数の NULL 値を許可します。 UNIQUE インデックスのカラムに接頭辞値を指定する場合、カラム値は接頭辞の長さ内で一意である必要があります。

PostgreSQL 13 https://www.postgresql.jp/document/13/html/indexes-unique.html

一意インデックスが宣言された場合、同じインデックス値を有する複数のテーブル行は許されなくなります。 NULL 値は同じ値とはみなされません。 複数列の一意インデックスは、インデックス列の全てが複数の行で同一の場合のみ拒絶されます。

なので例えば NULL を許容する expired_at TIMESTAMP という有効期限を複合キーに持つ UNIQUE 制約は意図通りに動作しないかもしれない。

CREATE TABLE prices (
  id           INTEGER   NOT NULL PRIMARY KEY,
  commodity_id INTEGER   NOT NULL,
  expired_at   TIMESTAMP, -- 最新の商品の価格は期限が NULL になっている想定だが……
  price        INTEGER   NOT NULL,
  FOREIGN KEY (commodity_id) REFERENCES commodities (id),
  UNIQUE (commodity_id, expired_at) -- NULL の重複は許すので expired_at が NULL の行は複数できる
)

この動きは標準 SQL に従った動きらしいけど詳しく調べていない。


今日のコミット。