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
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.