postgresql - 排除软删除行的唯一约束

Question

我们有一个具有唯一约束的表，用于收集一个用户留下的反馈，对于另一个用户，与销售有关。

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 IS NOT NULL，则认为反馈已删除，尽管我们的数据库中仍有审计线索(并且可能会向站点管理员显示它)。

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

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

Answer 1

您的唯一索引，稍后被删除。

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

您的唯一索引至少有两个可能会给您带来麻烦的副作用。

1. 在其他表中，您不能设置引用“反馈”的外键约束。外键引用需要将某些列组合声明为 primary key 或 unique。
2. 您的唯一索引允许多个行仅在“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 将这种索引记录为部分索引，如果您需要在某个时候用 Google 搜索它。其他平台对其使用不同的术语——过滤索引 就是其中之一。您可以使用一对部分索引将问题限制在一定程度上。

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

CREATE UNIQUE INDEX feedback_unique_user_subject_and_sale_not_null
ON feedback(user_id, subject_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, subject_id, sale_id)
);

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