通过检查添加可延迟的唯一约束

发布于 2025-01-16 07:15:32 字数 507 浏览 1 评论 0原文

我想添加一个带有检查的可延迟唯一约束，例如

ALTER TABLE locations 
   ADD CONSTRAINT short_name 
    UNIQUE (hallway, row, module, level, position, zone_id) 
    CHECK (active) DEFERRABLE

但这是不可能的。

上下文：我有一个表，其中给定的字段组合必须是唯一的，仅当字段活动设置为 true 时（可能的检查？）。通常，我们会收到必须作为原子事务执行的更新列表，这就是约束必须可延迟的原因。

这个问题的答案显示为选项一唯一索引，但它不可推迟。

有没有可能的解决方案或解决方法？

原文

I would like to add a deferrable unique constraint with a check, such as

ALTER TABLE locations 
   ADD CONSTRAINT short_name 
    UNIQUE (hallway, row, module, level, position, zone_id) 
    CHECK (active) DEFERRABLE

But it is not possible.

Context: I have a table in which a given combination of fields must be unique, just when the field active is set on true (Possible CHECK?). Normally we receive a list of updates that must be performed as an atomic transaction, that is why the constraint must be deferrable.

The answer to this question shows as an option one UNIQUE INDEX, but it is not deferrable.

Is there any possible solution or workaround?

分享到QQ

分享到微博