Rails中混合复合键索引配置：jsonb字段+数据库列

Absolutely! You can absolutely set up a unique composite index that combines a nested field from your jsonb column (meta_data->>'phone_number') and the regular department_id foreign key. Here's a step-by-step breakdown to make this work smoothly:

1. Generate a Migration File #

First, create a migration to add the index. Run this in your terminal:

rails generate migration AddUniqueIndexToUsersOnPhoneNumberAndDepartmentId

2. Write the Migration Code #

Open the generated migration file and add the index using PostgreSQL's JSONB operator ->> to extract the phone_number value from the meta_data column. We'll also explicitly name the index to avoid PostgreSQL's default name length limits:

class AddUniqueIndexToUsersOnPhoneNumberAndDepartmentId < ActiveRecord::Migration[7.0]
  def change
    # Combine the nested jsonb field and department_id for a unique index
    add_index :users, ["meta_data->>'phone_number'", :department_id], 
              unique: true, 
              name: 'index_users_on_phone_number_and_department_id'
  end
end

For Older Rails Versions (pre-5.x) #

If you're working with an older Rails version that doesn't support directly passing JSONB expressions to add_index, use raw SQL instead:

class AddUniqueIndexToUsersOnPhoneNumberAndDepartmentId < ActiveRecord::Migration
  def up
    execute <<-SQL
      CREATE UNIQUE INDEX index_users_on_phone_number_and_department_id 
      ON users ((meta_data->>'phone_number'), department_id);
    SQL
  end

  def down
    remove_index :users, name: 'index_users_on_phone_number_and_department_id'
  end
end

3. Add Model-Level Validation (Optional but Recommended) #

Database indexes act as the final guard against duplicates, but adding model-level validation will give users friendlier error messages instead of raw database exceptions. To do this, create a virtual attribute for phone_number to simplify validation:

class User < ApplicationRecord
  # Virtual attribute to access meta_data's phone_number easily
  def phone_number
    meta_data&.fetch('phone_number', nil)
  end

  def phone_number=(value)
    self.meta_data ||= {}
    self.meta_data['phone_number'] = value.strip
  end

  # Validate uniqueness across phone_number and department_id
  validates :phone_number, 
            uniqueness: { scope: :department_id, message: "is already taken for this department" },
            presence: true
  validates :department_id, presence: true
end

Key Notes to Keep in Mind #

• PostgreSQL Only: This approach relies on PostgreSQL's jsonb support—make sure your database is PostgreSQL (it won't work with SQLite or MySQL).
• Handling NULL Values: PostgreSQL treats NULL values as distinct, so if phone_number can be NULL, multiple users with NULL phone numbers and the same department_id won't trigger the unique constraint. If you want to prevent this, add a where clause to the index:

  add_index :users, ["meta_data->>'phone_number'", :department_id], 
            unique: true, 
            name: 'index_users_on_phone_number_and_department_id',
            where: "meta_data->>'phone_number' IS NOT NULL"
  

• Index Name Length: Always explicitly name your composite indexes with JSONB fields—PostgreSQL's auto-generated names can get too long and cause errors.

Run rails db:migrate to apply the index, and you're all set! This setup ensures no two users share the same phone_number (stored in meta_data) and department_id combination.

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