如何在事务数据库中存储已删除的行
这是 heximal 在我的其他帖子中提出的评论的后续内容。我想要一个 deleted_on 列来检测已删除的记录，对于这些记录，十六进制表明该列是多余的。
这是他的评论：
您要使用
deleted_xx字段来确定记录已删除吗？恕我直言，最好、最好的方法是添加记录一些布尔数据类型的活动属性（例如名为
ACTIVE的字段）。因此，为了“删除”记录，我们必须更新ACTIVE字段和update_date、updated_by中的值单个 UPDATE 查询。为了选择所有活动记录，我们只需进行如下查询：从 MyTable 中选择 *，其中 ACTIVE=1
我知道 Oracle 应用程序使用这种方法，并且我同意
我还阅读了以下帖子：
- 将已删除的行存储在表中
- http://www.udidahan.com/2009/09/01 /不要删除-只是-不要/
- http://ayende.com/Blog/archive/2009/ 08/30/avoid-soft-deletes.aspx
我的问题是：如何按照建议在具有 isActive 标志的表上设置唯一约束上面的十六进制。我的所有表中都有代理键。但我想确保自然键列（我们称之为业务键列）具有唯一的约束。
如果我有一个 deleted_on 字段来跟踪删除，那么我可以包含此列作为自然键约束的一部分。因此，它允许多个已删除的记录具有相同的业务键组合，仅deleted_on 日期字段不同。
如果我有 isActive 字段并使用 last_updated_on 列来跟踪删除日期，则我必须在自然键约束上有 2 个选项
- ，我可以包含
isActive作为我自然关键约束的一部分。但这将允许最多仅删除一条具有相同业务键组合的记录。 - 我可以将
isActive加上last_updated_on作为自然键约束的一部分。但我发现有一个额外的列deleted_on 会让事情变得更容易。
有什么想法吗？我在这里错过了什么吗？
This is a follow-up to the comment raised by heximal on one of my other posts. I want to have a deleted_on column to detect deleted records to which heximal suggested that this column is redundant.
Here is his comment:
You're going to use
deleted_xxfields to determine that the record is deleted?imho, the best and most nice way is to add to record some activity attribute of boolean datatype (e.g. the field named
ACTIVE). So in order to "delete" record, we must update the value ofACTIVEfield andupdate_date,updated_byin single UPDATE query. In order to select all active records we just have to make query like:SELECT * FROM MyTable WHERE ACTIVE=1
I know that Oracle Applications use such approach, and I agree
I also read the following posts:
- Store deleted rows in a table
- http://www.udidahan.com/2009/09/01/dont-delete-just-dont/
- http://ayende.com/Blog/archive/2009/08/30/avoid-soft-deletes.aspx
My question is: How to set the unique constraint on a table which has a isActive flag as suggested by heximal above. I do have surrogate keys in all my tables. But I want to ensure the natural key columns (what we call business key columns) have a unique constraint.
If I have a deleted_on field to track deletes, then I can include this column as part of the natural key constraint. So, it allows more than one deleted record with the same business key combination, differing only in the deleted_on date field.
If I have isActive field and use last_updated_on column to track the deletion date, I have to 2 options on the natural key constraint
- I can include
isActiveas part of my natural key constraint. But this would allow max only one deleted record with the same business key combination. - I can include
isActivepluslast_updated_onas part of natural key constraint. But I see having an extra column deleted_on makes it easier.
Any thoughts? Am I missing something here?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
“我的问题是如何在具有 isActive 标志的表上设置唯一约束，如上面的十六进制建议的那样。我的所有表中都有代理键。但我想确保自然键列（我们称之为业务键列） ）有一个唯一的约束。
如果我有一个 returned_on 字段来跟踪删除，那么我可以将此列作为自然键约束的一部分，因此，它允许多个具有相同业务键组合的已删除记录，仅在日期字段已删除。”
即使将deleted_on DATE字段作为自然键的一部分，您仍然无法在同一天删除-重新插入-删除所有内容。可能看起来是病态的，但你真的能确定病态的情况永远不会发生吗？
如果您的数据库需要反映以下事实：某些内容是“活跃的”，即与当前业务高度相关，而其他一些内容是“非活跃的”，例如，保留其某些内容的唯一原因时间用于归档目的，然后通过定义两个表来设计数据库以反映这一事实：一个包含“活动”内容，另一个包含“已归档”内容。也许您可以使用触发器在发生删除时自动实现“移动到存档”。
这样做，您可以让 DBMS 使用“活动”表上的唯一约束强制执行您的自然键，并且可以在“存档”表中包含删除时间戳，甚至可能不需要在以下位置定义任何键：都在那张桌子上。
"My question is how to set the unique constraint on a table which has a isActive flag as suggested by heximal above. I do have surrogate keys in all my tables. But I want to ensure the natural key columns (what we call business key columns) have a unique constraint.
If I have a deleted_on field to track deletes, then I can include this column as part of the natural key constraint. So, it allows more than one deleted record with the same business key combination, differing only in the deleted_on date field."
Even with a deleted_on DATE field as part of your natural key, you still couldn't delete-reinsert-delete all on the same day. May seem pathological, but can you really be certain that the pathological case will NEVER occur ?
If your database needs to reflect the fact that some of the content is "active" in the sense that it is highly relevant for current business, and some other content is "inactive", e.g. in the sense that the only reason for keeping it some time is for archival purposes, then design your database to reflect that fact by defining two tables: one with the "active" content and one with the "archived" content. Perhaps you can use a trigger to automatically achieve the "move-to-archive" whenever a delete occurs.
Doing so, you can have your natural key enforced by the DBMS using a unique constraint on the "active" table, and you can include a deletion-timestamp in the "archive" table, and you may not even need to define any key at all on that table.
添加“VERSION”（整数）而不是 ACTIVE 列怎么样？
当然，这个版本字段可以很容易地成为自然密钥的一部分。
What about adding a "VERSION" (Integer) instead of an ACTIVE column?
This Version field can easily be part of the Natural Key, of course.
我正要写一篇大文章，突然想起我已经写过
此“是否存在问题”使用 EAV 表的软删除解决方案？
如果您想要的是撤消删除的简单方法，或者为了审计/故障排除目的而保留删除的跟踪记录，那么使用镜像表是一个简单的解决方案。
和
软删除最佳实践 (PHP/MySQL)
您需要考虑“删除”的真正含义。似乎您希望能够重建历史记录，在这种情况下，术语“删除”会引起混乱。确实不是删除。您希望改用某种时态数据模型，并引入有效日期的概念。
或者，如果您只关心数据量，则删除或移动早于 X 天/年的行的简单批处理也是一种非常简单的方法。
I was about to write a big essay when I remembered I already wrote it in
Are there problems with this 'Soft Delete' solution using EAV tables?
If what you want is a simple way of undoing a delete, or keeping a track record of the deletes for audit/troubleshooting purposes, using a mirror table is an easy solution.
and
Soft delete best practices (PHP/MySQL)
You need to think about what "delete" really means. It seems like you want to be able to reconstruct history in which case the term "delete" causes confusion. It really isn't a delete. You want to be using some kind of temporal data model instead and introduce the concept of effective dates.
Or if you only care about the data volumes, a simple batch process to DELETE or move rows older than X days/years is also a really simple way.