如何在Rails中跨表用scope实现room_number按facility唯一验证？

需求背景：现有rooms表（字段：id, room_number）和facility_rooms关联表（字段：id, facility_id, room_id（外键关联rooms.id）），需要保证同一设施（facility_id）下，房间号（room_number）唯一，即rooms.room_number + facility_rooms.facility_id的组合唯一。

一、模型层面验证 #

由于Rails默认的uniqueness验证仅支持当前表字段作为scope，跨表验证需要自定义逻辑，推荐在FacilityRoom模型中实现——该模型直接持有facility_id和关联的room_id，逻辑更直接：

class FacilityRoom < ApplicationRecord
  belongs_to :room
  belongs_to :facility

  # 自定义验证：检查当前facility_id与关联room的room_number组合是否唯一
  validate :unique_room_number_for_facility

  private

  def unique_room_number_for_facility
    # 更新场景下排除自身，避免误判重复
    existing_record = FacilityRoom.joins(:room)
                                  .where(facility_id: facility_id, rooms: { room_number: room.room_number })
                                  .where.not(id: id)
                                  .exists?

    if existing_record
      errors.add(:room_id, "该房间号在当前设施下已存在")
    end
  end
end

二、数据库层面约束（必加） #

仅靠模型验证无法避免并发场景下的重复写入，必须添加数据库级别的联合唯一索引，从底层保证数据一致性，同时提升验证查询的效率。

PostgreSQL 迁移写法 #

class AddUniqueIndexOnFacilityIdAndRoomNumber < ActiveRecord::Migration[7.0]
  def change
    # 创建表达式索引，关联facility_id和对应room的room_number
    add_index :facility_rooms, 
              [:facility_id, "(SELECT room_number FROM rooms WHERE rooms.id = facility_rooms.room_id)"],
              unique: true,
              name: "idx_facility_rooms_facility_id_room_number"
  end
end

MySQL 8.0+ 迁移写法 #

MySQL 8.0及以上版本支持函数索引，写法如下：

class AddUniqueIndexOnFacilityIdAndRoomNumber < ActiveRecord::Migration[7.0]
  def change
    add_index :facility_rooms, 
              [:facility_id, Arel.sql("(SELECT room_number FROM rooms WHERE rooms.id = facility_rooms.room_id)")],
              unique: true,
              name: "idx_facility_rooms_facility_id_room_number"
  end
end

执行迁移后，数据库会直接拦截重复的组合写入，彻底杜绝数据重复问题。

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