可以使用Rails / ActiveRecord中允许的NULL指定唯一索引吗？ [英] Possible to specify unique index with NULLs allowed in Rails/ActiveRecord?

问题描述

我想在列上指定唯一索引，但我还需要允许 NULL 值（多个记录可以具有 NULL 值）。在PostgreSQL上进行测试时，我看到我可以有一条记录，记录中的 NULL 值，但是下一条将引起问题：

I want to specify a unique index on a column, but I also need to allow NULL values (multiple records can have NULL values). When testing with PostgreSQL, I see that I can have 1 record with a NULL value, but the next will cause an issue:

irb(main):001:0> u=User.find(5)
  User Load (111.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 5]]
=> #<User id: 5, email: "[e-mail]", created_at: "2013-08-28 09:55:28", updated_at: "2013-08-28 09:55:28">
irb(main):002:0> u.email=nil
=> nil
irb(main):003:0> u.save
   (1.1ms)  BEGIN
  User Exists (4.8ms)  SELECT 1 AS one FROM "users" WHERE ("users"."email" IS NULL AND "users"."id" != 5) LIMIT 1
   (1.5ms)  ROLLBACK
=> false

因此，即使数据库允许，Rails也会首先检查是否用户存在一个不同的ID，并且电子邮件列设置为 NULL 。有没有一种方法，不仅数据库可以允许它，而且Rails也不会像上面一样先检查？

So even if the database allows it, Rails first checks to see if a User exists with a different id and with the email column set to NULL. Is there a way that not only the database can allow it, but Rails will not check first like above as well?

这个想法是用户不必输入电子邮件，但如果需要，我需要能够通过他们的电子邮件查找用户。我知道我可以创建另一个模型来将用户与电子邮件相关联，但是我宁愿以上述方式进行操作。

The idea is users don't have to enter an email, but if they do I need to be able to find a user by their email. I know I can create another model to associate users to emails, but I'd much rather do it the above way.

UPDATE ：这是我创建的用于添加电子邮件列的迁移代码：

class AddEmailToUsers < ActiveRecord::Migration
  def change
    add_column :users, :email, :string
    add_index :users, :email, :unique => true
  end
end

这是我添加到 User 模型：

validates :email, uniqueness: true

我忘记了我已将 validates 调用添加到用户模型。因此，Rails首先检查是有意义的。我想唯一的问题是数据库是否具有唯一索引和 NULL 字段是否安全？有没有一种方法可以在Rails中指定我要验证电子邮件的唯一性，除非它为 nil ？

I forgot that I had added the validates call to the User model. So that makes sense that Rails is checking first. I guess the only other question is if it's safe for databases to have a unique index and NULL fields? Is there a way to specify in Rails that I want to validate the email is unique unless it's nil?

推荐答案

您的迁移将正常进行，并将允许多个 null 值（对于大多数数据库引擎）。

Your migration will work and will allow multiple null values (for the most database engines).

但是对用户类的验证应如下所示。

But your validation for the user class should look like below.

validates :email, uniqueness: true, allow_nil: true

这篇关于可以使用Rails / ActiveRecord中允许的NULL指定唯一索引吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！