部分インデックスでミスった話

新たにカラムを追加することになり、その条件が

となったので、アーハイハイ、部分インデックスですねと思って設定していて、テストもしていたのだけれど、考慮漏れがあったので自戒を込めて書いておきます。

インデックスの条件にwhere句を使うことができるやつです。

よくあるパターンでは、メールアドレスで登録されたユーザーがユニークになるようにしてあるけれど、論理削除を考慮する場合などで使われたり、上位500件のデータのみにインデックスを貼りたいとか、そういうときに使います。

-- 論理削除されていないユーザーでメールアドレスがユニークであること
CREATE UNIQUE INDEX
  email_idx_deleted_at
ON users(email)
WHERE deleted_at IS NULL;

これってemailカラムは普通NOT NULL制約があるので通常はこれでも大体いけますし、Railsのバリデーションでもvalidates :email, presence: trueにしているので、空文字が入ることはありませんでした。

そう、空文字です。

先ほどの条件のカラムをtokenという名前のカラムとします。最初は全く同じ条件で部分インデックスを定義していました。

CREATE UNIQUE INDEX
  token_idx_deleted_at
ON users(token)
WHERE deleted_at IS NULL;

Railsのバリデーションの条件は、以下の通り。

validates :token, allow_blank: true, uniqueness: { scope: :deleted_at }

これで大丈夫かなと思っていたのですが、フォームから空文字が送られてきていて、tokenが空文字のデータが2つ入ることができずにエラーが発生。

テストでは重複NGなことと、論理削除済みならば重複OKであることなどは検証していたのですが、空文字の考慮が抜けていました…。

一通りどうしようか悩みました。

1で行こうかとも思ったのですが、SQLで直接空文字を入れられたら同じことが起きるので、2のほうを選びました。

CREATE UNIQUE INDEX
  token_idx_deleted_at
ON users(token)
WHERE
  deleted_at IS NULL
  AND
  token != '';

今後は気をつけようと思います。

patorashのブログ