how to set unique constraint over 2 columns when one column can be null

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?

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

You can't do that with a UNIQUE constraint in 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.

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

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.