Conditional unique indexes in Rails and PostgreSQL

This is a simple example of adding a conditional and partial unique index to Rails applications backed by PostgreSQL.

To demonstrate adding a conditional unique index I will use a following User model that I want to add:

Users in this case belongs to a project and function in one of the three possible roles. However to make our project team perform better we want to allow only one team leader on the team.

To build such unique index we can use simple SQL query:

It’s a composed index on project_id and a role that applies the unique constrain only on the specific conditions after the WHERE clause. In our case that would be role number 0 (:team_leader).

If we are going to add the migration for the User model it could look similar to this one:

If we are adding the table we add the index afterwards and if we are removing the table we remove the index beforehand.

Now if we try to add two team leaders to a project ActiveRecord complains about violating unique constraint:

Join the Conversation


Leave a comment

Your email address will not be published. Required fields are marked *