# Validate\_uniqueness\_of question

**URL:** <https://www.ruby-forum.com/t/validate-uniqueness-of-question/76358>\
**Category:** Rails\
**Created:** [November 17, 2006, 9:33pm UTC](https://www.ruby-forum.com/t/validate-uniqueness-of-question/76358 "2006-11-17T21:33:16Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![lwtan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/97f17d/32.png) [@lwtan](https://www.ruby-forum.com/u/lwtan)\
**Post date:** [November 17, 2006, 9:33pm UTC](https://www.ruby-forum.com/t/validate-uniqueness-of-question/76358/1 "2006-11-17T21:33:16Z")

</div>

Good day everyone,

I am trying to validate the uniqueness of my indexes (2 indexes) in a  
table before an insert. Can I use validate\_uniqueness\_of to do this? It  
seems it can only validate uniqueness for a column, but not for a  
combination of 2 or more.

Example: a table has a PK of column#1 and column#2.

Thanks in advance,

Will T.

---

<div class="post-metadata">

**Author:** ![lwtan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/97f17d/32.png) [@lwtan](https://www.ruby-forum.com/u/lwtan)\
**Post date:** [November 17, 2006, 9:59pm UTC](https://www.ruby-forum.com/t/validate-uniqueness-of-question/76358/2 "2006-11-17T21:59:27Z")

</div>

Make a unique index in the database, and write this:

begin  
ModelName.create  
rescue Exception =\> e

# interrogate e to determine what sort of failure it was

# and take the appropriate action.

end

This is the right way to do this. Testing before insert doesn’t  
work because it’s possible that after you test, but before you  
insert, another process may have inserted a duplicate!

–  
– Tom M., CTO  
– Engine Y., Ruby on Rails Hosting  
– Reliability, Ease of Use, Scalability  
– (866) 518-YARD (9273)

---

<div class="post-metadata">

**Author:** ![lwtan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/97f17d/32.png) [@lwtan](https://www.ruby-forum.com/u/lwtan)\
**Post date:** [November 17, 2006, 10:09pm UTC](https://www.ruby-forum.com/t/validate-uniqueness-of-question/76358/3 "2006-11-17T22:09:29Z")

</div>

On 11/17/06, Will T. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Thanks in advance,
> 
> Will T.
> 
> –  
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).
> 
> >

Will,

I’ve only used multiple column in indexes when scoping relationships  
like the following:

```
create_table :accounts, :force => true do |t|
  t.column :name, :string, :null => false, :limit => 50
  ...
end

create_table :users, :force => true do |t|
  t.column :account_id, :integer, :null => false
  t.column :email_address, :string
  ...
end

add_index :users, [:account_id, :email_address], :unique => true

```

Then you can use:

```
validates_uniqueness_of :email_address, :scope => :account_id,

```

:message =\> ‘There is already another user with this email address.’

It sounds like if your PK is made of two columns you might want to  
just override before\_validation(). Rails has never had great  
composite key functionality although I think there are some plugins  
out there that might ease the pain…

Hope this helps.

–  
Zack C.  
[http://depixelate.com](http://depixelate.com)

---

<div class="post-metadata">

**Author:** ![lwtan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/97f17d/32.png) [@lwtan](https://www.ruby-forum.com/u/lwtan)\
**Post date:** [November 17, 2006, 11:40pm UTC](https://www.ruby-forum.com/t/validate-uniqueness-of-question/76358/4 "2006-11-17T23:40:40Z")

</div>

Tom M. wrote:

> Make a unique index in the database, and write this:
> 
> begin  
> ModelName.create  
> rescue Exception =\> e
> 
> # interrogate e to determine what sort of failure it was
> 
> # and take the appropriate action.
> 
> end
> 
> This is the right way to do this. Testing before insert doesn’t  
> work because it’s possible that after you test, but before you  
> insert, another process may have inserted a duplicate!
> 
> –  
> – Tom M., CTO  
> – Engine Y., Ruby on Rails Hosting  
> – Reliability, Ease of Use, Scalability  
> – (866) 518-YARD (9273)

I agree with Tom that defining a unique key in your DB and letting the  
DB enforce it is the best way to go.

I question the usefulness of validates\_uniqueness\_of in general because:

1. validates\_uniqueness\_of can’t guarantee that the field you are about  
to save to the DB is indeed unique (as Tom explained)

2. validates\_uniqueness\_of needs to query the DB in order to validate  
your model, which defeats the whole purpose of an application-side  
validation (IMO)

I started a discussion about this on my blog, and no one has  
successfully made a case for using validates\_uniqueness\_of yet. If  
anybody out there does have a scenario where it makes sense to use this  
validation, please let me know so I can stop criticizing it 🙂  
[http://allyourdatabase.blogspot.com/2006/11/generating-activerecord-data.html](http://allyourdatabase.blogspot.com/2006/11/generating-activerecord-data.html)

Cheers.

---

<div class="post-metadata">

**Author:** ![lwtan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/97f17d/32.png) [@lwtan](https://www.ruby-forum.com/u/lwtan)\
**Post date:** [November 18, 2006, 1:41am UTC](https://www.ruby-forum.com/t/validate-uniqueness-of-question/76358/5 "2006-11-18T01:41:54Z")

</div>

On 11/17/06, Bryan E. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> > 
> 
> I agree with Tom that defining a unique key in your DB and letting the
> 
> >

Bryan,

I think validates\_uniqueness\_of does have a “valid” place. It lets  
you keep your code DRY and works in 99.999% of the cases. It only  
fails in the very rare case where another insert occurs after one  
thread (or mongrel process) has checked for uniqueness. In that very  
rare case the db’s unique index would catch it and the database would  
stay valid. The only side effect is that the app would render an  
error page to the user. I’m willing to bet that this would likely  
never happen until a site got really big, and in even then it would  
have to be the perfect storm.

–  
Zack C.  
[http://depixelate.com](http://depixelate.com)

---

<div class="post-metadata">

**Author:** ![lwtan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/97f17d/32.png) [@lwtan](https://www.ruby-forum.com/u/lwtan)\
**Post date:** [November 18, 2006, 1:35am UTC](https://www.ruby-forum.com/t/validate-uniqueness-of-question/76358/6 "2006-11-18T01:35:46Z")

</div>

I tried using drysql for my application ([http://drysql.rubyforge.org/](http://drysql.rubyforge.org/)  
for my guide) and kept getting this NameError message.

I have a table called “room\_types” and a class called “RoomType”

My controller looks like:

def new  
require\_gem ‘drysql’  
@room\_type = RoomType.new  
end

My model looks like:

class RoomType \< ActiveRecord::Base  
set\_table\_name “room\_types”  
end

Any insight to this matter is much appreciated,

Will T.

Bryan E. wrote:

> Tom M. wrote:
> 
> > Make a unique index in the database, and write this:
> > 
> > begin  
> > ModelName.create  
> > rescue Exception =\> e
> > 
> > # interrogate e to determine what sort of failure it was
> > 
> > # and take the appropriate action.
> > 
> > end
> > 
> > This is the right way to do this. Testing before insert doesn’t  
> > work because it’s possible that after you test, but before you  
> > insert, another process may have inserted a duplicate!
> > 
> > –  
> > – Tom M., CTO  
> > – Engine Y., Ruby on Rails Hosting  
> > – Reliability, Ease of Use, Scalability  
> > – (866) 518-YARD (9273)
> 
> I agree with Tom that defining a unique key in your DB and letting the  
> DB enforce it is the best way to go.
> 
> I question the usefulness of validates\_uniqueness\_of in general because:
> 
> 1. validates\_uniqueness\_of can’t guarantee that the field you are about  
> to save to the DB is indeed unique (as Tom explained)
> 
> 2. validates\_uniqueness\_of needs to query the DB in order to validate  
> your model, which defeats the whole purpose of an application-side  
> validation (IMO)
> 
> I started a discussion about this on my blog, and no one has  
> successfully made a case for using validates\_uniqueness\_of yet. If  
> anybody out there does have a scenario where it makes sense to use this  
> validation, please let me know so I can stop criticizing it 🙂  
> [All Your Database Are Belong To Me: Generating ActiveRecord Data Validations Automatically](http://allyourdatabase.blogspot.com/2006/11/generating-activerecord-data.html)
> 
> Cheers.

---

<div class="post-metadata">

**Author:** ![lwtan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/97f17d/32.png) [@lwtan](https://www.ruby-forum.com/u/lwtan)\
**Post date:** [November 18, 2006, 5:58am UTC](https://www.ruby-forum.com/t/validate-uniqueness-of-question/76358/7 "2006-11-18T05:58:09Z")

</div>

On 11/17/06, Bryan E. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Evaluating the validation on a more subjective criterion, I don’t  
> believe that using validates\_uniqueness\_of provides any DRYness to my  
> application code that can’t be achieved by extending ActiveRecord::Base  
> to handle a duplicate key error returned from the DB. Given that  
> validates\_uniqueness\_of needs to be defined multiple times in your  
> application code (once for each unique key), I think that defining a  
> generic duplicate key error handler in ActiveRecord::Base is actually a  
> more DRY approach, but I suppose that it is a matter of personal taste.

I agree. If only the databases and their drivers provided these info in  
a  
more standard, accessible way. I tried parsing postgres contraint  
violation  
errors and it’s a real pain to deduce which table and columns were  
affected,  
especially since these messages are locale dependent and change between  
versions.

That said, a common way to introspect database _errors_ in addition to  
tables + columns would be wonderful.

jeremy

---

<div class="post-metadata">

**Author:** ![lwtan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/97f17d/32.png) [@lwtan](https://www.ruby-forum.com/u/lwtan)\
**Post date:** [November 18, 2006, 6:22am UTC](https://www.ruby-forum.com/t/validate-uniqueness-of-question/76358/8 "2006-11-18T06:22:16Z")

</div>

Jeremy K. wrote:

> On 11/17/06, Bryan E. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:
> 
> > Evaluating the validation on a more subjective criterion, I don’t  
> > believe that using validates\_uniqueness\_of provides any DRYness to my  
> > application code that can’t be achieved by extending ActiveRecord::Base  
> > to handle a duplicate key error returned from the DB. Given that  
> > validates\_uniqueness\_of needs to be defined multiple times in your  
> > application code (once for each unique key), I think that defining a  
> > generic duplicate key error handler in ActiveRecord::Base is actually a  
> > more DRY approach, but I suppose that it is a matter of personal taste.
> 
> I agree. If only the databases and their drivers provided these info in  
> a  
> more standard, accessible way. I tried parsing postgres contraint  
> violation  
> errors and it’s a real pain to deduce which table and columns were  
> affected,  
> especially since these messages are locale dependent and change between  
> versions.
> 
> That said, a common way to introspect database _errors_ in addition to  
> tables + columns would be wonderful.
> 
> jeremy

Good point, Jeremy.  
I think you’ve identified a hole in my argument

…and you’ve also given me an excellent idea for a future enhancement  
to DrySQL: [http://drysql.rubyforge.org/](http://drysql.rubyforge.org/)

---

<div class="post-metadata">

**Author:** ![lwtan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/97f17d/32.png) [@lwtan](https://www.ruby-forum.com/u/lwtan)\
**Post date:** [November 18, 2006, 5:39am UTC](https://www.ruby-forum.com/t/validate-uniqueness-of-question/76358/9 "2006-11-18T05:39:29Z")

</div>

Zack C. wrote:

> On 11/17/06, Bryan E. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:
> 
> > > 
> > 
> > I agree with Tom that defining a unique key in your DB and letting the
> > 
> > >
> 
> Bryan,
> 
> I think validates\_uniqueness\_of does have a “valid” place. It lets  
> you keep your code DRY and works in 99.999% of the cases. It only  
> fails in the very rare case where another insert occurs after one  
> thread (or mongrel process) has checked for uniqueness. In that very  
> rare case the db’s unique index would catch it and the database would  
> stay valid. The only side effect is that the app would render an  
> error page to the user. I’m willing to bet that this would likely  
> never happen until a site got really big, and in even then it would  
> have to be the perfect storm.
> 
> –  
> Zack C.  
> [http://depixelate.com](http://depixelate.com)

Hi Zack

I agree with you that it would be a rare occurrance for a duplicate  
value to be inserted into the DB after ActiveRecord does the uniqueness  
check and before it actually saves your record.

However, it is possible. As well, your DB already does a duplicate key  
check on insert/update (assuming that you defined a unique key on the  
DB), so the uniqueness check done by the validation is redundant  
database I/O and can’t be guaranteed to be accurate. In terms of  
functionality and performance, using the validation actually seems to me  
to be detrimental to an application, rather than beneficial.

Evaluating the validation on a more subjective criterion, I don’t  
believe that using validates\_uniqueness\_of provides any DRYness to my  
application code that can’t be achieved by extending ActiveRecord::Base  
to handle a duplicate key error returned from the DB. Given that  
validates\_uniqueness\_of needs to be defined multiple times in your  
application code (once for each unique key), I think that defining a  
generic duplicate key error handler in ActiveRecord::Base is actually a  
more DRY approach, but I suppose that it is a matter of personal taste.

From my perspective, the application-side validations’ prime purpose is  
to provide “fast” model validation by avoiding DB I/O. If an  
application-side validation needs to perform redundant DB I/O in order  
to validate an object, I can’t see the advantage of using it, especially  
if it can’t even guarantee model validity.

This is just my opinion, and I respect yours as well.

Cheers.