数据库记录的物理与逻辑(硬与软)删除? [英] Physical vs. logical (hard vs. soft) delete of database record?

问题描述

与实际或物理删除记录相比，对记录进行逻辑/软删除(即设置表明记录已被删除的标志)有什么优势?

What is the advantage of doing a logical/soft delete of a record (i.e. setting a flag stating that the record is deleted) as opposed to actually or physically deleting the record?

这是常见的做法吗?

这样安全吗?

推荐答案

优点是您可以保留历史记录(有利于审计)并且您不必担心通过数据库中引用的各种其他表级联删除您要删除的行.缺点是您必须编写任何报告/显示方法以将标志考虑在内.

Advantages are that you keep the history (good for auditing) and you don't have to worry about cascading a delete through various other tables in the database that reference the row you are deleting. Disadvantage is that you have to code any reporting/display methods to take the flag into account.

就如果这是一种常见做法而言 - 我会说是的，但是对于任何事情，您是否使用它取决于您的业务需求.

As far as if it is a common practice - I would say yes, but as with anything whether you use it depends on your business needs.

想到另一个缺点 - 如果表上有唯一索引，删除的记录仍将占用一个"记录，因此您也必须围绕这种可能性进行编码(例如，具有用户名的唯一索引；已删除的记录仍会阻止已删除用户的用户名以获取新记录.解决此问题，您可以将 GUID 添加到已删除的用户名列，但这是我不推荐的一种非常hacky 的解决方法.可能在那个在这种情况下，最好有一个规则，即一旦用户名被使用，它就永远不能被替换.)

Thought of another disadvantange - If you have unique indexes on the table, deleted records will still take up the "one" record, so you have to code around that possibility too (for example, a User table that has a unique index on username; A deleted record would still block the deleted users username for new records. Working around this you could tack on a GUID to the deleted username column, but it's a very hacky workaround that I wouldn't recommend. Probably in that circumstance it would be better to just have a rule that once a username is used, it can never be replaced.)

这篇关于数据库记录的物理与逻辑(硬与软)删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！