Add compose unique index in Rails migration

Configure migration

Add the following to your migration:

add_index :table_name, [:column_name_a, :column_name_b], unique: true

For example, suppose you have a users and authors table, and you don’t want duplicate author entries with the same name and user_id. You can ensure this sort of uniqueness with the following:

# frozen_string_literal: true

class CreateAuthors < ActiveRecord::Migration[7.0]
  def change
    create_table :authors do |t|
      t.references :user, null: false, foreign_key: true
      t.string :name, null: false

      t.timestamps
    end

    add_index :authors, %i[user_id name], unique: true
  end
end

Add Active Record validation

In addition to adding a database-level index, you’ll want to use ActiveRecord validations to handle duplicate entries more gracefully. So, if we had an Author model that must have a unique combination of name and user_id, we’d add a validation like the following:

# frozen_string_literal: true

class Author < ApplicationRecord
  belongs_to :user

  validates :name, presence: true, uniqueness: { scope: :user_id }
end

Now, whenever a single user attempts to create an author with a duplicate name, we will get an ActiveRecord validation error instead of a database error like this:

ERROR:  duplicate key value violates unique constraint "index_authors_on_name_and_user_id" (PG::UniqueViolation)

Test Active Record validation

Install Shoulda Matchers and add the following tests to your model spec:

# frozen_string_literal: true

require "rails_helper"

RSpec.describe Author, type: :model do
  describe "validations" do
    subject { build(:author) }

    it { is_expected.to validate_presence_of(:name) }
    it { is_expected.to validate_uniqueness_of(:name).scoped_to(:user_id) }
  end
end

Make sure you define a subject in the block where you add the validate_uniqueness_of validation. Refer to the Shoulda Matchers docs for why you need a subject.