Uniqueness Constraint Between Two Columns in Rails
Ruby on Rails allows us to define uniqueness between two database table columns (i.e. two model attributes). At the time of this writing, I couldn’t find official Rails documentation that shows how to do this in both the migration and the model, hence this post.
Defining a Uniqueness Constraint in the Migration
Creating a uniqueness constraint in a migration requires us to add an index on two columns/attributes. The key here is to place the attributes in an array and set them to be unique as a pair. The line in the migration looks like this:
t.index [:attribute, :another_attribute], unique: true
Here’s a more realistic example. Assume the following:
- We have an existing
Account
model - We want to create a new table for a
Statement
model. - An account can have many statements
- A statement belongs to one account
- We want a maximum of 1 statement per date, per account (which means we need the
date
andaccount_id
attributes to be unique together)
To accomplish the above, we add indexes to date
and account_id
and set them to be unique as a pair.
class CreateStatements < ActiveRecord::Migration[6.0]
def change
create_table :statements do |t|
t.references :account, foreign_key: true, index: true
t.date :date
# date and account_id are unique as a pair
t.index [:date, :account_id], unique: true
end
end
end
Now an Account can have many Statements, but only a maximum of 1 per date. Without this constraint, an existing statement with today’s date would prevent any other statements from being created today, even for separate accounts.
Uniqueness constraints can also be added after a table has been created. All we need to do is add the indexes to both attributes like before. Also, instead of using t.index
we use add_index
:
class AddUniqueIndexToStatements < ActiveRecord::Migration[6.1]
def change
add_index :statements, [:account_id, :date], unique: true
end
end
Model Validation
Now that the constraint is set in the database, we can also add it in the model. To do that, we add a uniqueness validation of an attribute and scope it to another attribute. For example, in the Statement model previously mentioned it looks like this:
validates :date, uniqueness: { scope: :account_id, message: "Statement already exists for this date." }
And that’s it! The model’s attributes are now unique as a pair and validated at both the database layer and model layer.