重新使用软删除的记录
如果我有一个表结构：
code, description, isdeleted
其中 code 是主键。
用户创建一条记录，然后将其删除。 因为我使用软删除，所以 isdeleted 将设置为 true。 然后在我的查询中，我将使用 where 子句而不是 isdeleted 进行选择
现在，如果用户要创建新记录，他们可能会看到代码“ABC”不存在，因此他们尝试重新创建它。 由于 where 子句的原因，select 语句不会找到它。 但会出现主键索引错误。
是否应该允许用户重新使用该记录？ 我认为不会，因为软删除的想法是保留旧数据查询的记录，以便连接到“已删除”记录仍然有效。 如果允许用户重复使用代码，那么他们可以更改描述，这可能会改变历史数据的视图。 但完全阻止他们使用该代码是否太严厉了？
或者我应该使用完全隐藏的主键，然后可以重新使用“代码”字段？
If I have a table structure that is:
code, description, isdeleted
where code is the primary key.
The user creates a record, then later on deletes it. Because I am using soft deletes the isdeleted will be set to true. Then in my queries I would be doing a select with the where clause and not isdeleted
Now if a user goes to create a new record they may see that code 'ABC' doesn't exist so they tried to recreate it. The select statement won't find it because of the where clause. But there will be a primary key index error.
Should the user be allowed to re-use the record? I would think not since the idea of the soft delete is to keep the record for queries on older data so that joins to the 'deleted' record still work. If the user was allowed to re-use the code then they could change the description which might change the view of the historical data. But is it too harsh to stop them from using that code at all?
Or should I be using a completely hidden primary key and then the 'code' field can be re-used?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我认为这取决于你所说的具体数据。
如果用户尝试重新创建代码“ABC”，那么它是否是上次使用的、现已退出的相同“ABC”，还是完全不同的“ABC”？
如果它实际上指的是同一现实世界的“事物”，那么简单地“取消删除”它可能不会有什么坏处。 毕竟 - 这是同一件事，所以从逻辑上讲，它应该在历史查询和新查询中显示为同一件事。 如果您的用户决定不再需要它，那么他们可以删除它，它就会消失。 如果将来某个时候他们再次需要它，他们可以通过再次添加它来有效地取消删除它。
然而，如果新的“ABC”指的是与旧的“ABC”不同的东西（在现实世界中），那么您可能会说“代码”实际上并不是主要的key，在这种情况下，如果您的数据没有提供任何其他自然选择，您也可以创建一个任意键。
当然，这样做的一个很大的缺点是，您必须非常小心，不要让用户使用相同的“代码”创建两个活动记录。
I think it depends on the specific data you're talking about.
If the user is trying to recreate code 'ABC', is it the SAME 'ABC' that was in use last time that has now come out of retirement, or is it a completely different 'ABC'?
If it actually refers to the same real-world 'thing', then there may be no harm in simply 'undeleting' it. After all - it's the same thing, so logically speaking it should show up as the same thing in historical and new queries. If your user decides they don't need it any more, then they can delete it and it'll go away. If at some point in the future they need it again, they can effectively un-delete it by adding it in again.
If, however, the new 'ABC' refers to something (in the real world) which is different to the old 'ABC', then you could argue that the 'code' isn't actually a primary key, in which case, if your data doesn't provide any other natural choice, you may just as well create an arbitrary key.
A big downside of this is that you'll have to be pretty careful not to let the user create two active records with the same 'code', of course.
当您选择记录（不包括软删除）以在用户界面/输出文件中显示它们时，请使用 where not isdeleted。
但当用户请求插入操作时，执行两次查询。
查找所有记录（忽略 isdeleted 值）。
基于第一个查询结果，如果存在则执行 UPDATE（并且反向 isdeleted 标志），或者如果不存在则执行 true INSERT。
基于
业务逻辑的细微差别取决于您。
When you select records (excluding soft-deletes) to display them in user interface/ output file, use where not isdeleted.
But when the user requests an insert operation, perform two queries.
Lookup all records (ignoring isdeleted value).
Based on first query result, perform an UPDATE if it exists (and reverse isdeleted flag) or perform a true INSERT if it does not exist.
The nuances of the business logic are up to you.
我已经使用用户表完成了此操作，其中电子邮件是唯一约束。 如果有人取消了该帐户，则仍然需要他们的信息来保证引用完整性，因此我将 is_deteled 设置为 true，并将“_deleted”添加到电子邮件字段。 这样，如果用户决定将来再次注册，对用户来说就没有问题，并且唯一约束也不会被打破。
我认为软删除在某些情况下是好的。 例如，如果某人从此网站删除了他们的帐户，而您删除了他们的用户，那么他们的所有帖子和答案都将丢失。 我认为软删除并将其用户显示为“已删除用户”或类似的东西要好得多......哦，我也相信分离的主键
I've done this with user tables, where the email is a unique constraint. If someone cancels there account, their information is still needed for referential integrity, so what I to is set is_deteled to true, and add '_deleted' to the email field. In this way, if the user decides to sign up again in the future, there is no problem for the user and the unique constraint is not broken.
I think soft delete is good in some situations. For example, if someone deleted their account from this site and you delete their user then all their posts and answers would be lost. I think it is much better to soft delete and display their user as "deleted user" or something similar... oh, I also believe in divorced primary keys
我想你自己已经很好地回答了这个问题。 如果您希望用户能够重新使用已删除的代码，那么您应该有一个用户不可见的单独主键。 如果代码的唯一性很重要，那么用户通常不应该输入它们。
I think you have answered this pretty well yourself. If you want the user to be able to re-use the deleted codes, then you should have a separate primary key not visisble to the user. If it is important that the codes be unique, then the users should generally not be entering them anyway.
我知道很多人都认为数据应该是自然的，但是如果您要支持软删除而不是总是重新使用以前的记录，那么您应该使用与数据完全分开的主键。出现这种情况。
拥有分离的主键将允许您拥有具有相同“代码”值的多个记录，并且它将允许您“取消删除”（否则，为什么要费心软删除？）一个值，而不必担心覆盖其他内容。
就我个人而言，我更喜欢 ID 的数字自动递增风格，但 GUID 的支持者也有很多。
I know many people have argued that the data should be natural, but you should be using a primary key that is completely separate from your data if you're going to be supporting soft deletes without the intention of always re-using the previous record when this situation arises.
Having a divorced primary key will allow you to have multiple records with the same 'code' value, and it will allow you to "undelete" (otherwise, why bother with a soft delete?) a value without worrying about overwriting something else.
Personally, I prefer the numeric auto-incremented style of ID, but there are many proponents of GUIDs.