如何将Rails模型的number字段校验改写为SQL数据库约束？

你在Rails模型中为number属性配置了如下校验规则，需要补充对应的数据库层约束，同时确认是否需要通过索引匹配scope、if/unless的判断逻辑：

validates :number,
  numericality: { greater_than: 0 },
  uniqueness: { scope: :tenant_id },
  unless: state == 'pending',
  if: :number

你当前写的迁移约束代码如下，不确定是否为最优实现：

def change
    reversible do |dir| 
        dir.up do execute <<-SQL
            ALTER TABLE work_orders
            ADD CONSTRAINT number_uniqueness_constraint
            CHECK ( number IS NULL OR number NOT IN
                ( SELECT number
                    FROM work_orders
                    WHERE tenant_id = work_orders.tenant_id )
                )
        SQL
     end
    end
  end

你写的CHECK约束实现存在三个硬伤，不建议使用：

• 性能极差：约束内的关联子查询会在每次插入、更新记录时扫描同租户下的所有number值，表数据量超过千级后写入性能会出现明显下降，数据量越大问题越严重
• 无法防住并发重复：在默认的事务隔离级别下，两个同时提交的同租户同number写入请求，可能同时通过子查询校验，最终还是会生成重复数据，数据库层约束的核心作用完全失效
• 和模型校验逻辑不匹配：既没有实现number > 0的数值校验，也没有排除state = 'pending'的记录，约束覆盖范围和模型规则不一致

数据库层面的唯一性保障必须依赖唯一索引，CHECK约束天生不适合做唯一性校验。结合你的校验规则，部分唯一索引（过滤索引） 是最优解，不需要写复杂的自定义CHECK约束，主流数据库（PostgreSQL、MySQL 8.0+）都支持该特性。

迁移代码 #

def change
  # 数值合法性约束：匹配numericality: { greater_than: 0 }规则
  execute <<-SQL
    ALTER TABLE work_orders
    ADD CONSTRAINT check_number_positive
    CHECK (number IS NULL OR number > 0)
  SQL

  # 部分唯一索引：同时匹配剩余所有校验规则
  add_index :work_orders, [:tenant_id, :number],
    unique: true,
    where: "state != 'pending' AND number IS NOT NULL"
end

逻辑匹配说明 #

• 不需要额外创建普通索引：这个联合唯一索引本身就可以覆盖tenant_id + number的查询场景，写入、查询性能都远高于子查询CHECK方案
• 索引过滤条件完全对齐模型的条件判断：
  • 加上state != 'pending'条件，待处理状态的记录不会进入索引，不会触发唯一性校验，对应模型的unless: state == 'pending'规则
  • 加上number IS NOT NULL条件，number为空的记录不会进入索引，不会触发校验，对应模型的if: :number规则
  • 索引本身按[:tenant_id, :number]建立唯一约束，直接实现同租户下number唯一的scope要求
• 数据库层面的唯一索引会从引擎层面阻止并发写入重复数据，完全不会出现CHECK约束的并发漏判问题

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