# How to validate uniqueness across multiple columns in ActiveRecord?

**URL:** <https://www.ruby-forum.com/t/how-to-validate-uniqueness-across-multiple-columns-in-activerecord/183944>\
**Category:** Rails\
**Created:** [February 22, 2010, 9:54pm UTC](https://www.ruby-forum.com/t/how-to-validate-uniqueness-across-multiple-columns-in-activerecord/183944 "2010-02-22T21:54:08Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Grary](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/91b2a8/32.png) [@Grary](https://www.ruby-forum.com/u/Grary)\
**Post date:** [February 22, 2010, 9:54pm UTC](https://www.ruby-forum.com/t/how-to-validate-uniqueness-across-multiple-columns-in-activerecord/183944/1 "2010-02-22T21:54:08Z")

</div>

Hi,

From a model class, what is the standard way of ensuring uniqueness  
across multiple columns (attributes) simultaneously?

In other words, I want to ensure only unique “rows” are persisted to  
storage. I feel like there must be a straightforward way to do this.  
Any suggestions?

Thanks,

Grar

---

<div class="post-metadata">

**Author:** ![Grary](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/91b2a8/32.png) [@Grary](https://www.ruby-forum.com/u/Grary)\
**Post date:** [February 22, 2010, 9:55pm UTC](https://www.ruby-forum.com/t/how-to-validate-uniqueness-across-multiple-columns-in-activerecord/183944/2 "2010-02-22T21:55:18Z")

</div>

On Feb 22, 8:52 pm, Grary [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Hi,
> 
> From a model class, what is the standard way of ensuring uniqueness  
> across multiple columns (attributes) simultaneously?
> 
> In other words, I want to ensure only unique “rows” are persisted to  
> storage. I feel like there must be a straightforward way to do this.  
> Any suggestions?

validate\_uniqueness’ scope option does this (but you should really be  
using a unique index as well.)

Fred

---

<div class="post-metadata">

**Author:** ![Grary](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/91b2a8/32.png) [@Grary](https://www.ruby-forum.com/u/Grary)\
**Post date:** [February 22, 2010, 10:56pm UTC](https://www.ruby-forum.com/t/how-to-validate-uniqueness-across-multiple-columns-in-activerecord/183944/3 "2010-02-22T22:56:11Z")

</div>

Thanks Fred.

What do you mean by ‘use a unique index’?

What I am concerned about is logical uniqueness, i.e., persist only  
data objects with a unique combination of attributes.

Thanks,

Grar

On Feb 22, 3:54 pm, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![Grary](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/91b2a8/32.png) [@Grary](https://www.ruby-forum.com/u/Grary)\
**Post date:** [February 23, 2010, 5:14pm UTC](https://www.ruby-forum.com/t/how-to-validate-uniqueness-across-multiple-columns-in-activerecord/183944/4 "2010-02-23T17:14:11Z")

</div>

Rick,

Thanks. That sounds exactly like what I’m after…

Grary

---

<div class="post-metadata">

**Author:** ![Grary](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/91b2a8/32.png) [@Grary](https://www.ruby-forum.com/u/Grary)\
**Post date:** [February 23, 2010, 8:09pm UTC](https://www.ruby-forum.com/t/how-to-validate-uniqueness-across-multiple-columns-in-activerecord/183944/5 "2010-02-23T20:09:12Z")

</div>

On 23 February 2010 01:30, Rick DeNatale [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> resolve it.  
> If you add a column called “combined\_params” or similar, you can add a  
> before\_validate method to concatenate all of your parameters and write  
> it to this column, this will let you do AR “validates\_uniqueness\_of”  
> checks too.

It would actually remove the need to have the key added across the  
columns in the DB, but I’d leave that as a safety net, as it would be  
too easy to accidentally (or deliberately) change a row’s  
“combined\_params” column, and risk duplicates.

---

<div class="post-metadata">

**Author:** ![Grary](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/91b2a8/32.png) [@Grary](https://www.ruby-forum.com/u/Grary)\
**Post date:** [February 23, 2010, 9:26pm UTC](https://www.ruby-forum.com/t/how-to-validate-uniqueness-across-multiple-columns-in-activerecord/183944/6 "2010-02-23T21:26:32Z")

</div>

Another fine idea, it would appear. But why do both? Does checking a  
combined parameters column before validation save me some kind of  
performance hit?

Grar

---

<div class="post-metadata">

**Author:** ![Grary](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/91b2a8/32.png) [@Grary](https://www.ruby-forum.com/u/Grary)\
**Post date:** [February 23, 2010, 9:44pm UTC](https://www.ruby-forum.com/t/how-to-validate-uniqueness-across-multiple-columns-in-activerecord/183944/7 "2010-02-23T21:44:06Z")

</div>

On 23 February 2010 20:25, Grary [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Another fine idea, it would appear. But why do both? Does checking a  
> combined parameters column before validation save me some kind of  
> performance hit?

Yes it does a little… but you have to weigh up what hits are going  
to happen (performance or otherwise) if non-unique rows get in the  
table.  
If your job is on the line, then having an index across the fields is  
bullet proof, and processors are cheap… (ish)

Being able to check “combined parameters” is just gravy - an added  
bonus that’ll make the Rails coding simpler, but the index is what  
making _sure_.

---

<div class="post-metadata">

**Author:** ![Grary](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/91b2a8/32.png) [@Grary](https://www.ruby-forum.com/u/Grary)\
**Post date:** [February 23, 2010, 2:33am UTC](https://www.ruby-forum.com/t/how-to-validate-uniqueness-across-multiple-columns-in-activerecord/183944/8 "2010-02-23T02:33:58Z")

</div>

On Mon, Feb 22, 2010 at 4:54 PM, Grary [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Thanks Fred.
> 
> What do you mean by ‘use a unique index’?
> 
> What I am concerned about is logical uniqueness, i.e., persist only  
> data objects with a unique combination of attributes.

So if I understand correctly, lets say there are two attributes a, and b

You want to make sure that no two models have the same COMBINATION of  
values for a and b,

so having two models with:

a = 1, b = 2  
a = 1, b = 3

would not be a conflict

If that’s the case then the standard validation

class Widget \< ActiveRecord::Base  
validates\_uniqueness\_of :a, :b  
end

wouldn’t work since it tries to prevent saving two models with the  
same value of a, OR with the same value of b

And even if that’s not what you’re trying to do, and you’re ok with  
the example being a conflict, Fred’s point is that  
validates\_uniqueness\_of doesn’t guarantee uniqueness if two users try  
to save conflicting records simultaneously. The validation works by  
first trying to find a record with the value, and if it doesn’t find  
it inserting the ‘new’ record, and this can fail due to a concurrency  
hole.

To fill this hole requires leaning on the database server, and the way  
to do that in SQL is by having a unique index on the table which  
covers the column or columns you want to be unique. This assume you  
are using a database which supports it, e.g. MySql.

To create an index you can create a migration which includes a statement  
like

add\_index :widgets, [:a, :b], :unique =\> true)

Assuming that the table name for the model is ‘widgets’

## Now if you do this, you also need to be aware that if you try to save a record with a uniqueness conflict the save will raise an ActiveRecord::StatementInvalid exception, which you’ll need to rescue and do something like telling the user of the conflict so that he can resolve it.

Rick DeNatale

Blog: [http://talklikeaduck.denhaven2.com/](http://talklikeaduck.denhaven2.com/)  
Twitter: [http://twitter.com/RickDeNatale](http://twitter.com/RickDeNatale)  
WWR: [NameBright - Coming Soon](http://www.workingwithrails.com/person/9021-rick-denatale)  
LinkedIn: [http://www.linkedin.com/in/rickdenatale](http://www.linkedin.com/in/rickdenatale)

---

<div class="post-metadata">

**Author:** ![Grary](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/91b2a8/32.png) [@Grary](https://www.ruby-forum.com/u/Grary)\
**Post date:** [February 23, 2010, 10:51pm UTC](https://www.ruby-forum.com/t/how-to-validate-uniqueness-across-multiple-columns-in-activerecord/183944/9 "2010-02-23T22:51:26Z")

</div>

@Robert - Yes, I read that portion of the documentation and thanks for  
the reminder.

Otherwise, of potential interest to discussants…

I now have a migration adding a unique index to a model, like so:

add\_index :projects, [:name, :street\_address, :city, :state, :zip],  
:unique =\> true

where only some subset of the attributes – [name, zip], [name, city,  
state], [street\_address, city, state], etc. – are required in the  
Project model.

In testing, only when all the properties named in my add\_index method  
are passed is the exception properly thrown.

For example:

```
assert_raise ActiveRecord::StatementInvalid do
  exceptionable_proj = Project.new
  exceptionable_proj.city = city
  exceptionable_proj.street_address = street_address
  exceptionable_proj.state = state
  exceptionable_proj.name = name
  exceptionable_proj.zip = zip
  exceptionable_proj.save

  exceptionable_proj2 = Project.new
  exceptionable_proj2.city = city
  exceptionable_proj2.street_address = street_address
  exceptionable_proj2.state = state
  exceptionable_proj2.name = name
  exceptionable_proj2.zip = zip
  exceptionable_proj2.save
end

```

The above passes, whereas the test below does not:

```
assert_raise ActiveRecord::StatementInvalid do
  exceptionable_proj3 = Project.new
  exceptionable_proj3.street_address = street_address2
  exceptionable_proj3.zip = zip2
  exceptionable_proj3.save

  exceptionable_proj4 = Project.new
  exceptionable_proj4.street_address = street_address2
  exceptionable_proj4.zip = zip2
  exceptionable_proj4.save
end

```

Grar

---

<div class="post-metadata">

**Author:** ![Grary](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/91b2a8/32.png) [@Grary](https://www.ruby-forum.com/u/Grary)\
**Post date:** [February 23, 2010, 10:01pm UTC](https://www.ruby-forum.com/t/how-to-validate-uniqueness-across-multiple-columns-in-activerecord/183944/10 "2010-02-23T22:01:56Z")

</div>

Just to be crystal clear, what a number of these replies are attempting  
to tell you is that you cannot rely on validates\_uniqueness\_of.

## Excerpt from the Rails docs on validation:

Concurrency and integrity

## Using this validation method in conjunction with ActiveRecord::Base#save does not guarantee the absence of duplicate record insertions, because uniqueness checks on the application level are inherently prone to race conditions. For example, suppose that two users try to post a Comment at the same time, and a Comment’s title must be unique. At the database-level, the actions performed by these users could be interleaved in the following manner:

If two separate requests are received at virtually the same instant then  
validate\_uniqueness\_of can fail silently. You will end up with  
duplicates in the database that your validation specifically tries to  
avoid.

This is why the unique index across the two columns is necessary. This  
can only be reliably prevented at the database level. If this index  
exists and the validates\_uniqueness\_of fails due to a race condition the  
database layer will raise and exception. So you need to be prepared for  
this, even when using validates\_uniqueness\_of, by rescuing from the  
possible exception.