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:
Here’s a more realistic example. In this example we have a banking Account model and a Statement model. An account can have many statements, but we want a maximum of 1 statement per date, per account. To do that, we add indexes to
account_id and set them to be unique as a pair.
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
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:
And that’s it! The model’s attributes are now unique as a pair and validated at both the database layer and model layer.