in Everything

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:

class User < ApplicationRecord
  belongs_to :project

  enum role: %w(team_leader designer programmer)
end

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:

CREATE UNIQUE INDEX unique_user_role_in_project ON users (project_id, role) WHERE role = 0;

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:

class AddUsers < ActiveRecord::Migration[5.0]
  def change
    reversible do |direction|
      direction.down do
        # Remove unique index
        execute <<-SQL
          DROP INDEX IF EXISTS unique_user_role_in_project;
        SQL
      end
    end

    create_table :users do |t|
      t.integer :project_id, null: false
      t.string :name, null: false
      t.integer :role, null: false

      t.timestamps
    end

    reversible do |direction|
      direction.up do
        # Partial unique index
        execute <<-SQL
          CREATE UNIQUE INDEX unique_user_role_in_project ON users (project_id, role) WHERE role = 0;
        SQL
      end
    end
  end
end

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:

ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "unique_user_role_in_project"
DETAIL:  Key (project_id, role)=(1, 0) already exists.

Write a Comment

Comment