如何在Rails Migration中为两个reference字段添加唯一索引

General Approach #

First, let's cover the basic way to add a unique composite index across two reference columns, whether you're creating a new table or modifying an existing one.

1. When Creating a New Table #

If you're setting up a new table with the two references, you can add the unique index directly after the create_table block (this keeps your code clean and explicit):

class CreateUserPermissions < ActiveRecord::Migration[7.0]
  def change
    create_table :user_permissions do |t|
      t.references :user, null: false, foreign_key: true
      t.references :permission, null: false, foreign_key: true

      t.timestamps
    end

    # Add unique composite index across both reference columns
    add_index :user_permissions, [:user_id, :permission_id], unique: true
  end
end

2. When Modifying an Existing Table #

If the table already exists and you just need to add the unique index, create a dedicated migration for this change:

class AddUniqueIndexToUserPermissions < ActiveRecord::Migration[7.0]
  def change
    add_index :user_permissions, [:user_id, :permission_id], unique: true
  end
end

Quick Notes:

• t.references automatically creates columns named [reference_name]_id, so those are the names you’ll use in the index.
• If your table already has duplicate pairs of these columns, the migration will fail—you’ll need to delete or merge those duplicates first before applying the index.

Applying This to Your Specific Migration Code #

Let’s assume your existing migration code looks like this (a typical join table scenario):

class CreateTeamMemberships < ActiveRecord::Migration[7.0]
  def change
    create_table :team_memberships do |t|
      t.references :team, null: false, foreign_key: true
      t.references :member, null: false, foreign_key: true

      t.timestamps
    end
  end
end

You have two valid options to add the unique index for the team and member references:

Option 1: Update the Existing Migration (If You Haven’t Run It Yet) #

If you haven’t executed rails db:migrate for this migration yet, you can edit it directly to include the index:

class CreateTeamMemberships < ActiveRecord::Migration[7.0]
  def change
    create_table :team_memberships do |t|
      t.references :team, null: false, foreign_key: true
      t.references :member, null: false, foreign_key: true

      t.timestamps
    end

    # Add composite unique index here
    add_index :team_memberships, [:team_id, :member_id], unique: true
  end
end

Option 2: Create a New Migration (If You’ve Already Run the Original) #

Never edit a migration that’s already been applied to your database—it can cause inconsistencies. Instead, generate a new migration:

Run this command in your terminal:

rails generate migration AddUniqueIndexToTeamMemberships

Then open the generated migration file and update it with the index:

class AddUniqueIndexToTeamMemberships < ActiveRecord::Migration[7.0]
  def change
    add_index :team_memberships, [:team_id, :member_id], unique: true
  end
end

Finally, run rails db:migrate to apply the change.

A quick tip: The order of columns in the index ([:team_id, :member_id]) affects query performance if you often filter by one column before the other, but it doesn’t impact the uniqueness enforcement itself.

内容的提问来源于stack exchange，提问作者Augusto Carmo