当一列可以为空时，如何对两列设置唯一约束

I have a very simple table

我有一个非常简单的表格

categories(parent_id, title)

I'm trying to set a unique constraint so that two categories cannot have the same title and parent.

我试图设置一个唯一的约束，这样两个类别就不能有相同的标题和父类。

class CreateCategories < ActiveRecord::Migration
  def change
    create_table :categories do |t|
      t.integer :parent_id
      t.string  :title, :null => false
    end
    add_index :categories, [:title, :parent_id], :unique => true   
  end
end

When parent_id is null it doesn't enforce uniqueness on the title which is what we need. Is it possible to make sure titles are unique for root categories as well?

当parent_id为null时，它不会对标题强制惟一性，这正是我们需要的。是否有可能确保标题对于根目录也是唯一的?

2 个解决方案

#1

You can create a unique index for that:

你可以为此创建一个唯一的索引:

CREATE UNIQUE INDEX ix_categories_root_title
    ON categories (title)
    WHERE parent_id IS NULL

You'd sleep much better at night than relying on triggers or application-level validations :P

与依赖触发器或应用程序级验证相比，你晚上的睡眠会好得多

#2

You can't do that with a UNIQUE constraint in PostgreSQL:

您不能在PostgreSQL中使用惟一的约束:

However, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule.

但是，在这个比较中，两个空值不被认为是相等的。这意味着即使存在唯一约束，也可以在至少一个受约束列中存储包含空值的重复行。这种行为符合SQL标准，但是我们听说其他SQL数据库可能不遵循这个规则。

The underlying problem is that x = NULL is false for all x in standard SQL.

潜在的问题是，对于标准SQL中的所有x, x = NULL都是假的。

You could enforce it for NULL parent_id values with a BEFORE INSERT and BEFORE UPDATE trigger but ActiveRecord doesn't know what triggers are so you'd have to maintain the trigger by hand. Alternatively, you could do it all in custom validations and hope that nothing touches your database without going through your model first.

可以在INSERT之前和UPDATE触发器之前强制执行NULL parent_id值，但是ActiveRecord不知道触发器是什么，所以必须手动维护触发器。或者，您可以在自定义验证中完成所有这些工作，并希望在没有首先遍历模型的情况下，不会有任何东西接触到您的数据库。

#1