我们有一个表格,它有一个独特的约束条件,用于从一个用户留下的反馈,另一个用于销售的反馈.
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”列而不是进行硬删除.
