Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

validates_uniqueness_of admits duplicate nil values? #5853

JulianKniephoff opened this Issue Apr 15, 2012 · 7 comments


None yet
5 participants

Let's assume we have the following simple ActiveRecord model

class Foo < ActiveRecord::Base
  validates_uniqueness_of :name

with an attribute name.

In Rails 3.0 I could not create two records with an empty name like this:


which is expected, since we have a duplicate for the name attribute; namely nil.

However, since around Rails 3.1 the same code does not fail. Is this intended? The validation still works as expected for duplicate values other than nil.


pixeltrix commented Apr 16, 2012

It's what I'd expect since the ANSI SQL standard for UNIQUE indexes allow multiple null values.


drogus commented Apr 16, 2012

I would expect that too. I guess we should update the docs.

@drogus drogus added a commit that referenced this issue May 20, 2012

@drogus drogus Fix `validates_uniqueness_off :field, :allow_nil => false`
Closes (#5853)

Uniqueness validator was not properly checking if there are any existing
records, when value was `nil` and column was text type. `nil` was
converted to string, which resulted in queries looking like:

SELECT 1 FROM "posts" WHERE "posts"."title" = '' LIMIT 1

instead of

SELECT 1 FROM "posts" WHERE "posts"."title" IS NULL LIMIT 1

drogus commented May 20, 2012

I played with it a bit more and it seems that it's not an issue of the default value, but a bug only text columns. I pushed a fix, so now behavior is consistent with master branch, but I'm wondering if we couldn't change the default to false in both 3-2-stable and master, since it was broken for text types anyway (and probably most people uses uniqueness for this). That way we will make this behavior consistent with ANSI SQL and will not break people apps that relied on :allow_nil => false by default.

/cc @pixeltrix @tenderlove @jonleighton

@drogus drogus closed this May 20, 2012

@drogus drogus reopened this May 20, 2012


pixeltrix commented May 21, 2012

I'm not sure this is correct - if the column in the database is set to NOT NULL then it won't have any NULL values and if it also has a UNIQUE constraint then it will only have one blank string value. We also have to consider than input from a form will have a blank string and not a nil value - what happens in those circumstances.

We should probably take the default value of :allow_nil from the column information shouldn't we?

@ghost ghost assigned drogus May 21, 2012

kyanny commented Jun 1, 2012

I encountered this problem because of upgrading rails from 3.2.3 to 3.2.5.

My workaround is using allow_blank: true option at uniqueness validation, but is it proper way?

class Foo < ActiveRecord::Base
  validates_uniqueness_of :name, allow_blank: true

senny commented Mar 9, 2013

@drogus @pixeltrix what is the status of this issue?


pixeltrix commented Jan 5, 2014

I couldn't reproduce this in a standalone script using SQLite so I'm closing

@pixeltrix pixeltrix closed this Jan 5, 2014

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment