排除软删除行的唯一约束
我们有一个表，它有一个唯一的约束，用于一个用户留下的反馈，另一个用户关于销售的反馈。
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子句。
回答 3
Stack Overflow用户
发布于 2012-11-07 07:04:18
你的唯一索引，后来被编辑掉了。
CREATE UNIQUE INDEX feedback_unique_user_subject_and_sale_null
ON feedback(user_id, subject_id, sale_id)
WHERE deleted_at IS NULL您的唯一索引至少有两个副作用，可能会给您带来一些麻烦。
在其他表中，您不能设置引用“
- ”的外键约束。外键引用需要声明为
unique.
- Your或
primary key的列的某种组合，唯一索引允许多个仅在"deleted_at“时间戳上不同的行。因此，有可能得到如下示例所示的行。这是否是一个问题是application-dependent.
示例
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.35PostgreSQL将这种类型的索引记录为部分索引，如果您需要使用谷歌搜索它的话。其他平台对此使用不同的术语--过滤索引就是其中之一。您可以使用一对部分索引将问题限制在一定程度上。
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“列，而不是进行硬删除。
Stack Overflow用户
发布于 2012-11-07 07:03:30
尽管PostgreSQL文档建议不要使用唯一索引而不是约束(如果点要有约束)，但您似乎可以这样做
CREATE UNIQUE INDEX feedback_unique_user_subject_and_sale
ON feedback(user_id, subject_id, sale_id)
WHERE deleted_at IS NULLStack Overflow用户
发布于 2021-11-16 15:11:30
您可以使用deleted_at签名创建约束。
ALTER TABLE feedback
ADD CONSTRAINT unique_user_subject_and_sale
UNIQUE (user_id, subject_id, sale_id, deleted_at)这不会产生像唯一索引这样的问题。
唯一的问题是删除用户反馈，创建一个新反馈，然后在同一时间再次删除它。
考虑到柱子的精确度，用户必须适应1微秒。让我们面对它，这是不可能的，即使他完成了，这些请求之间的时间肯定会大于1微秒。
唯一的问题是null值不是唯一的。所以它不会起作用。
但是您可以为未删除的行添加一个默认值，作为您可以存储在该列中的最低值。
如果您不能接受deleted_at的默认值，如果对象没有被删除，您可以考虑添加一个deleted_token列，如果您删除了一个值，则可以为它生成一个键，对于未删除的对象，可以保留0或其他值。
https://stackoverflow.com/questions/13260456
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号