Rails: Validate unique combination of 3 columns

Question

Hi I wan't to validate the unique combination of 3 columns in my table.

Let's say I have a table called cars with the values :brand, :model_name and :fuel_type.

What I then want is to validate if a record is unique based on the combination of those 3. An example:

    brand    model_name    fuel_type
    Audi     A4            Gas
    Audi     A4            Diesel
    Audi     A6            Gas

Should all be valid. But another record with 'Audi, A6, Gas' should NOT be valid.

I know of this validation, but I doubt that it actually does what I want.

    validates_uniqueness_of :brand, :scope => {:model_name, :fuel_type}

Hmm okay - just thought that what it did was to check if :brand is unique within a group on :model_name, :fuel_type — Niels Kristian, Sep 15 '11 at 09:57
possible duplicate of [Rails validate uniqueness of multiple columns?](http://stackoverflow.com/questions/4870961/rails-validate-uniqueness-of-multiple-columns) — potashin, Jul 19 '15 at 01:44

score 131 · Accepted Answer · edited Jul 10 '17 at 12:14

131

There is a syntax error in your code snippet. The correct validation is :

validates_uniqueness_of :car_model_name, :scope => [:brand_id, :fuel_type_id]

or even shorter in ruby 1.9.x:

validates_uniqueness_of :car_model_name, scope: [:brand_id, :fuel_type_id]

with rails 4 you can use:

validates :car_model_name, uniqueness: { scope: [:brand_id, :fuel_type_id] }

with rails 5 you can use

validates_uniqueness_of :car_model_name, scope: %i[brand_id fuel_type_id]

edited Jul 10 '17 at 12:14

aelor

10,892
3
32
48

answered Jun 06 '12 at 08:36

alup

2,961
1
21
12

2

Is the rails 4 example really correct? I'd think it should be something like: `validates :brand_id, uniqueness: { scope: [:fuel_type_id] }` (i.e. don't reference the model name). – krsyoung Feb 19 '16 at 01:04
15

:model_name Is referencing the OP's question about car models lol not the model class :P – bkunzi01 May 20 '16 at 00:13
1

Oh yeah, that was misleading :( – Krzysztof Witczak Nov 04 '16 at 08:49

score 18 · Answer 2 · edited Jun 28 '17 at 14:28

18

Depends on your needs you could also to add a constraint (as a part of table creation migration or as a separate one) instead of model validation:

add_index :the_table_name, [:brand, :model_name, :fuel_type], :unique => true

Adding the unique constraint on the database level makes sense, in case multiple database connections are performing write operations at the same time.

edited Jun 28 '17 at 14:28

leviathan

11,080
5
42
40

answered Jan 11 '15 at 18:22

Alexander

7,484
4
51
65

1

When I tried this, it appears that the unique constraint it put on all columns individually – Daniel Hitzel Jul 13 '17 at 14:03
This works, however if someone tries to save a duplicated entry the server will respond with an Internal Server Error (500) message, which is not a desired behavior. To avoid this you need to add the validation at the Model level, that way it will respond with an error message just like any other validation. – tafuentesc Oct 05 '18 at 19:58
1

@tafuentesc you write the controller so you decide what will be the server response. You can wrap your code with `rescue` (try-catch) and respond on Duplicate error the way you want. – Alexander Oct 05 '18 at 23:36
As @tafuentesc mentioned it's highly recommended to add a validation in the model to prevent from raising `ActiveRecord::RecordNotUnique` error, at least in **Rails 5**. The validations could be something like `validates_uniqueness_of :model_name, scope: %i[fuel_type brand], message: "the given name already exists"` – alexventuraio Aug 11 '19 at 17:14

Ruby Junior Dev · Answer 3 · 2017-01-24T18:57:34.123

5

To Rails 4 the correct code with new hash pattern

validates :column_name, uniqueness: {scope: [:brand_id, :fuel_type_id]}

edited Jan 24 '17 at 18:57

answered Apr 22 '15 at 17:34

Ruby Junior Dev

118
1
4

score 4 · Answer 4 · answered Sep 15 '11 at 10:07

I would make it this way:

validates_uniqueness_of :model_name, :scope => {:brand_id, :fuel_type_id}

because it makes more sense for me:

there should not be duplicated "model names" for combination of "brand" and "fuel type", vs
there should not be duplicated "brands" for combination of "model name" and "fuel type"

but it's subjective opinion.

Of course if brand and fuel_type are relationships to other models (if not, then just drop "_id" part). With uniqueness validation you can't check non-db columns, so you have to validate foreign keys in model.

You need to define which attribute is validated - you don't validate all at once, if you want, you need to create separate validation for every attribute, so when user make mistake and tries to create duplicated record, then you show him errors in form near invalid field.

Hi, I think you're correct, but I'm not clear on how the logic works. I get the sense of validating on "unique model per brand and fuel type," but I don't get why it's wrong to say, "unique brand per model and fuel type." Hmm. — listenlight, Jun 30 '16 at 16:47
Yeah, I have the same question as @tidelake. Why every response said to `validate model_name` with `:scope => {:brand_id, :fuel_type_id}`? Why not the same way the OP mentioned validate `:brand` with `:scope => {:model_name, :fuel_type}` ? Why the proposed order is different from the OP's question? I do not see what's the problem or way all answer say to validate `model_name` instead of `brand`!!! — alexventuraio, Aug 10 '19 at 19:34

score 3 · Answer 5 · answered Mar 04 '20 at 22:16

Piecing together the other answers and trying it myself, this is the syntax you're looking for:

validates :brand, uniqueness: { scope: [:model_name, :fuel_type] }

I'm not sure why the other answers are adding _id to the fields in the scope. That would only be needed if these fields are representing other models, but I didn't see an indication of that in the question. Additionally, these fields can be in any order. This will accomplish the same thing, only the error will be on the :model_name attribute instead of :brand:

validates :model_name, uniqueness: { scope: [:fuel_type, :brand] }

Correct, appending _id is not necessary in the case of columns. — NBSamar, Sep 16 '21 at 11:25

score 2 · Answer 6 · answered Nov 28 '15 at 11:46

Using this validation method in conjunction with ActiveRecord::Validations#save does not guarantee the absence of duplicate record insertions, because uniqueness checks on the application level are inherently prone to race conditions.

This could even happen if you use transactions with the 'serializable' isolation level. The best way to work around this problem is to add a unique index to the database table using ActiveRecord::ConnectionAdapters::SchemaStatements#add_index. In the rare case that a race condition occurs, the database will guarantee the field's uniqueness.

Rails: Validate unique combination of 3 columns

6 Answers6