postgresql – 排除了软删除行的唯一约束

发布时间：2020-12-13 16:17:12 所属栏目：百科来源：网络整理

导读：我们有一个表格,它有一个独特的约束条件,用于从一个用户留下的反馈,另一个用于销售的反馈. ALTER TABLE feedbackADD CONSTRAINT unique_user_subject_and_saleUNIQUE (user_id,subject_id,sale_id) 这可以确保我们不会意外地获得重复的反馈行. 目前,我们有时

我们有一个表格,它有一个独特的约束条件,用于从一个用户留下的反馈,另一个用于销售的反馈.

ALTER TABLE feedback
ADD CONSTRAINT unique_user_subject_and_sale
UNIQUE (user_id,subject_id,sale_id)

这可以确保我们不会意外地获得重复的反馈行.

目前,我们有时会硬错误地删除错误的反馈并让用户再次离开.我们想要更改为软删除：

ALTER TABLE feedback
ADD COLUMN deleted_at timestamptz

如果deleted_at不是NULL,请考虑删除反馈,尽管我们的数据库中仍然有审计跟踪(并且可能会向网站管理员显示幻像).

当我们使用像这样的软删除时,我们如何保持我们的唯一约束？是否可以在不使用更通用的CHECK()约束的情况下进行聚合检查(我从未尝试过使用像这样的检查约束).

这就像我需要在约束中附加一个WHERE子句.

你的独特索引,后来被删除了.

CREATE UNIQUE INDEX feedback_unique_user_subject_and_sale_null
ON feedback(user_id,sale_id)
WHERE deleted_at IS NULL

您的唯一索引至少有两个可能导致您遇到麻烦的副作用.

>在其他表中,您不能设置引用“反馈”的外键约束.外键引用要求将某些列组合声明为主键或唯一.
>您的唯一索引允许多行仅在“deleted_at”时间戳中有所不同.因此,最终可能会出现类似于下面示例的行.这是否是一个问题取决于应用程序.

例

user_id  subject_id  sale_id  deleted_at
--
1        1           1        2012-01-01 08:00:01.33
1        1           1        2012-01-01 08:00:01.34
1        1           1        2012-01-01 08:00:01.35

PostgreSQL将这种索引记录为部分索引,如果有时需要谷歌它.其他平台使用不同的术语 – 过滤索引是一个.您可以使用一对部分索引在一定程度上限制问题.

CREATE UNIQUE INDEX feedback_unique_user_subject_and_sale_null
ON feedback(user_id,sale_id)
WHERE deleted_at IS NULL

CREATE UNIQUE INDEX feedback_unique_user_subject_and_sale_not_null
ON feedback(user_id,sale_id)
WHERE deleted_at IS NOT NULL

但我认为没有理由去解决这个问题,特别是考虑到外键的潜在问题.如果你的表看起来像这样

create table feedback (
  feedback_id integer primary key,user_id ...
  subject_id ...
  sale_id ...
  deleted_at ...
  constraint unique_user_subj_sale 
    unique (user_id,sale_id)
);

那么你所需要的只是对{user_id,sale_id}的唯一约束.您可以进一步考虑使所有删除使用“deleted_at”列而不是进行硬删除.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!