您将如何将此模型验证作为SQL约束？
我在一个模型中对number属性进行了验证，我还想添加一个数据库约束。您将如何做？您是否会添加索引或部分索引来满足范围和if语句？
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
I have this validation in a Model for a number attribute, and I also want to add a database constraint. How would you do this? would you add an index or partial index to satisfy the scope and the if statements?
validates :number,
numericality: { greater_than: 0 },
uniqueness: { scope: :tenant_id },
unless: state == 'pending',
if: :number
I'm thinking about this migration to create the constraint but I'm not sure it is the best way
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
业务限制应在数据库中定义为硬性约束（如果可能的话）以及导轨中的硬性约束。
在数据库中拥有约束的优点是，这严格确保您的数据集有效，因为数据库将不允许打破约束。然而，这样做的缺点是，如果有一个查询试图打破约束，则错误消息将不是一个很好的用户体验。
在Rails中，您会收到每个约束的好消息，并且可以很好地处理错误。这非常适合EG格式约束（例如数值检查）。但是，对于唯一性约束，种族条件有可能，就像在并行请求中一样，唯一性检查和后来的提交可能会重叠，从而导致失败的约束。
因此，在两个地方定义约束通常是一个好主意。在大多数情况下，Rails约束将导致良好的错误消息，即使有种族，SQL约束也将严格确保有效的数据。
话虽这么说，您的轨道约束目前并不适应您的Postgres约束。除非规则，否则您会缺少
。对于唯一约束，您也可以使用更简单的组合了两列的唯一约束，而是根据您打算如何确切地解决边缘案例（即，否则 and>>如果铁轨验证的规则）。Business constraints should be defined both in the database as a hard constraint (if possible) as well as within Rails.
The advantage of having the constraint within your database is that this strictly ensures that your dataset is valid as the database will not allow to break the constraint. The disadvantage of this however is that if there is a query which tries to break the constraint, the error message will not be a great user experience.
Within Rails, you get great messages for each constraint and can handle errors very well. This works great for e.g format constraints (such as the numericality check). For uniqueness constraints however, there is the possibility for race conditions, as in parallel requests, the uniqueness check and later commit can overlap, resulting in a failed constraint.
Because of that, it is often a good idea to define constraints in both places. The Rails constraint will result in good error messages for most cases, the SQL constraint will strictly ensure valid data, even when there are races.
With that being said, your Rails constraint currently doesn't matych your Postgres constraint. You are missing the
unlessrule. For the uniqueness constraint, you may also use a much simpler combined UNIQUE constraint for the two columns, rather that writing your own sub-query, depending on how exactly you plan to resolve your edge-cases (i.e. theunlessandifrules of your Rails validation).