SQL Schema 设计题——删除标志

Question

在我们的数据库模式中，我们喜欢使用删除标志。删除记录后，我们会更新该字段，而不是运行删除语句。我们的其余查询然后在返回数据时检查删除标志。

问题是:

删除标志是一个日期，默认值为NULL。这很方便，因为当一条记录被删除时，我们可以很容易地看到它被删除的日期。

但是，为了正确实现唯一约束，我们需要在唯一约束中包含删除标志。问题是，在 MS SQL 上，它的行为符合我们的要求(对于此设计)，但在 postgresql 中，如果多列唯一约束中的任何字段为 NULL，则允许该字段。这种行为符合 SQL 标准，但它使我们的设计崩溃。

我们正在考虑的选项是:

1. 将已删除字段的默认值设置为某个硬编码日期

2. 为删除添加一个位标志，然后每个表将有 2 个删除相关字段 - date_deleted 和 is_deleted(例如)

3. 将 date_deleted 更改为 is_deleted(位域)

我怀疑选项 1 会影响性能，每个查询都必须检查硬编码日期，而不仅仅是检查 IsNUll。再加上感觉不对。

选项 2 也感觉不对 - “已删除”的 2 个字段是非干的。

选项 3，我们丢失了“日期”信息。有一个 modified 字段，理论上它会反射(reflect)删除日期，但仅假设对行的最后更新是对删除位的更新。

那么，有什么建议吗？你过去做了什么来处理“删除标志”？

更新感谢大家的 super 快速和周到的回复。我们最终使用了一个简单的 bool 字段和一个修改日期字段(带有触发器)。我刚刚注意到部分索引建议，这看起来像是解决这个问题的完美方法(但我还没有真正尝试过)

Answer 1

如果仅保留已删除的记录对您很重要，您是否考虑过将它们移至历史表？

• 这可以通过触发器轻松实现。
• 应用程序逻辑不需要考虑这个已删除的标志。
• 从中进行选择时，您的表格将保持精简和简洁。
• 它将通过唯一索引解决您的问题。