postgresql 可以使用 Rails/ActiveRecord 中允许的 NULL 指定唯一索引吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18496223/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Possible to specify unique index with NULLs allowed in Rails/ActiveRecord?
提问by at.
I want to specify a unique index on a column, but I also need to allow NULLvalues (multiple records can have NULLvalues). When testing with PostgreSQL, I see that I can have 1 record with a NULLvalue, but the next will cause an issue:
我想在列上指定唯一索引，但我还需要允许NULL值（多个记录可以有NULL值）。使用 PostgreSQL 进行测试时，我看到我可以有 1 个带有NULL值的记录，但下一个会导致问题：
irb(main):001:0> u=User.find(5)
User Load (111.1ms) SELECT "users".* FROM "users" WHERE "users"."id" = LIMIT 1 [["id", 5]]
=> #<User id: 5, email: "[email protected]", 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
So even if the database allows it, Rails first checks to see if a Userexists with a different id and with the emailcolumn 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?
因此，即使数据库允许，Rails 也会首先检查是否User存在具有不同 id 且email列设置为 的 a NULL。有没有办法不仅数据库可以允许它，而且Rails也不会像上面那样首先检查？
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: Here's the migration code I had created to add the emailcolumn:
更新：这是我为添加email列而创建的迁移代码：
class AddEmailToUsers < ActiveRecord::Migration
def change
add_column :users, :email, :string
add_index :users, :email, :unique => true
end
end
And here's the code I had added to the Usermodel:
这是我添加到User模型中的代码：
validates :email, uniqueness: true
I forgot that I had added the validatescall to the Usermodel. 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 NULLfields? Is there a way to specify in Rails that I want to validate the email is unique unless it's nil?
我忘记了我已将validates调用添加到User模型中。因此，Rails 首先进行检查是有道理的。我想唯一的另一个问题是数据库拥有唯一索引和NULL字段是否安全？有没有办法在 Rails 中指定我想验证电子邮件是否唯一，除非它是nil？
回答by aross
Your migration will work and will allow multiple nullvalues (for the most database engines).
您的迁移将起作用并且将允许多个null值（对于大多数数据库引擎）。
But your validation for the user class should look like below.
但是您对用户类的验证应该如下所示。
validates :email, uniqueness: true, allow_nil: true
回答by rokob
To clarify why this works at the database level, you have to understand the three-valued logic used in SQL: true, false, null.
为了阐明为什么这在数据库级别有效，您必须了解 SQL 中使用的三值逻辑：true, false, null。
nullis typically taken to mean unknown, therefore its semantics in operations are usually equivalent to not knowing what that particular value is and seeing if you can still work out an answer. So for instance 1.0 * nullis nullbut null OR trueis true. In the first case, multiplication by an unknown is unknown, but in the second, the second half of the conditional makes the whole statement always true so it doesn't matter what is on the left side.
null通常被认为是未知的，因此它在操作中的语义通常相当于不知道那个特定的值是什么，看看你是否仍然可以找到答案。所以例如1.0 * nullis nullbut null OR trueis true。在第一种情况下，乘以未知数是未知的，但在第二种情况下，条件的后半部分使整个语句始终为真，因此左侧的内容无关紧要。
Now when it comes to indexes, the standard does not specify anything so vendors are left to interpret what unknown means. Personally, I think a unique index should be defined as in the PostgreSQL docs:
现在，当谈到索引时，该标准没有指定任何内容，因此供应商只能解释未知的含义。就个人而言，我认为应在 PostgreSQL 文档中定义唯一索引：
When an index is declared unique, multiple table rows with equal indexed values will not be allowed
当索引被声明为唯一时，将不允许具有相同索引值的多个表行
The question should then be what is the value of null = null? The correct answer should be null. So if you read a bit between the lines of those PostgreSQL docs and say that a unique index will disallow multiple rows for which the equality operator returns true for said value then multiple nullvalues should be allowed. This is exactly how PostgreSQL works, so in that setup you can have a unique column with multiple rows having nullas a value.
那么问题应该是 的价值是null = null什么？正确答案应该是null。因此，如果您在这些 PostgreSQL 文档的两行之间阅读了一些内容，并说唯一索引将不允许多个行，而相等运算符为所述值返回 true，那么null应该允许多个值。这正是 PostgreSQL 的工作方式，因此在该设置中，您可以拥有一个具有多个行null作为值的唯一列。
On the other hand, if you wanted to interpret the definition of a unique index to be disallow multiple rows for which the inequality operator does not return false, then you would not be able to have multiple rows with nullvalues. Who would choose to operate in this contrapositive setup? This is how Microsoft SQL Server chooses to define a unique index.
另一方面，如果您想将唯一索引的定义解释为不允许不等式运算符不返回 false 的多行，那么您将无法拥有多行带有null值。谁会选择在这种相反的设置中操作？这是 Microsoft SQL Server 选择定义唯一索引的方式。
Both of these ways of defining a unique index are correct based on the 2003 SQL standard's definition of null. So it really depends on your underlying database. But that being said, I think the majority operate similar to PostgreSQL.
根据 2003 SQL 标准对null. 所以这真的取决于你的底层数据库。但话虽如此，我认为大多数操作类似于 PostgreSQL。
