Posts Tagged ‘database’

4th April
2010
written by simplelight

Assuming 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, 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 rectify it.