1

In my migration file I have a variable whose value should be unique or nil. How can I achieve such? My current setup generates all sorts of validation errors, I think because nil values are not unique and in the current set up it wants to see a unique value.

I currently have:

Migration file:

class CreateUsers < ActiveRecord::Migration
  def change
    create_table :users do |t|
      t.string   :my_var
      ...
    end
  end
end

class AddIndex < ActiveRecord::Migration
  def change
    add_index :users,  :my_var,    unique: true
  end
end

Model file:

validates :my_var, uniqueness: true

Is there a way to allow it to be nil, to require a unique value if it has a value, and to make it an index?

Nick
  • 3,496
  • 7
  • 42
  • 96
  • Do you mean to allow exactly *one* row with a NULL value or any number of rows with a NULL value? You can have unique constraints and / or indices for either case. 1: http://stackoverflow.com/a/8289253/939860. 2: http://stackoverflow.com/a/20154518/939860 – Erwin Brandstetter Jan 22 '16 at 05:05

1 Answers1

2

As for your model validation, you can make it like this:

validates :my_var, uniqueness: { allow_nil: true }

OR, if you want to include empty strings (i.e. "")

validates :my_var, uniqueness: { allow_blank: true }

But, in any case, you'll have to drop your unique index

EDIT: The index part may not be necessary, as noted in the comments below.

Omnigazer
  • 831
  • 4
  • 7
  • Thanks for explaining. So there's no way to make it an index if I want to allow `nil` values? Because this value will be used for searching for a user, so making it an index would definitely be valuable. – Nick Jan 21 '16 at 22:05
  • It depends on the database system, but if I recall correctly postgresql allows multiple `NULL` in unique index. If not you can use a partial or filtered index `where my_var is not null` – jazzytomato Jan 21 '16 at 22:14
  • @Nick, well, whether you can use unique index here is database-dependent. In PostgreSQL you can get away with it. In any case, if you're okay with your model validation enforcing the uniqueness, you can just build a regular index, it will do just as fine. – Omnigazer Jan 21 '16 at 22:15
  • Thanks, that's great. I can confirm now that I added `allow_nil` in the model validation that having the index is no problem. – Nick Jan 21 '16 at 22:23