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 (: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.
Get Test Driving Rails and make your tests faster and easier to maintain.
