当涉及到外键时，硬删除工作如何工作 [英] How can Hard Deletes work when Foreign Keys are involved
问题描述
我很乐意同意。但是没有人似乎提到如何处理一个主要的，不可避免的问题：当外键约束阻止其删除时，当级联删除不是一个选项时，如何一个硬删除一行？ / p>
本质上，如果要强制删除实体A，实体B正在指向它，出于业务原因B根本无法删除，你不能使用某种形式的软删除（即将记录保留在哪里）？
如果你有一个客户Acme已经交易10001，你不能简单地硬删除Acme，因为几乎任何业务都需要：
- 没有任何交易可以被删除
- 交易必须指向使用它的客户
<例如，无论Acme如何删除（硬，软，超中等）销售报告仍然必须显示，交易＃10001是由客户Acme在2010-05-01发布的 - 即使Acme不在周围。所以它需要Acme至少在系统中存储 。
一个线程从硬删除的倡导者中不断出现：使用适当的审计表，并做一些事情，例如保留已删除对象的序列化副本。但是，我们不能将事务＃10001的FK重定向到审计表中的这一行。然后怎样呢？创建一个反映客户模式的OldCustomers表，并以某种方式重定向指向Acme的系统中的每个FK？我不是DB专家，所以我不知道这是否可能，但即使是这样，现在所有的报告查询都要考虑两个表（听起来比不得不附加和IsDeleted = false 所有查询）
简而言之，我觉得我缺少一些东西，因为硬删除是辩论 - 意味着它们都是可行的选择。但据我所知，在95％的商业案例中，没有可能使用硬删除（在技术层面上，由于FK约束，在逻辑层面上，只是因为一件事情不能在另一件事情上消失指向它）
我使用了大量使用软删除的时间设计。或者通过执行相同的查询as of现在获取当前数据。该设计允许硬删除（很少使用），软删除（最常使用）和公司删除（如软删除，但不允许撤消）。这个设计允许一个执行一个查询截止一个日期，并返回相同的结果，如果在该日期执行查询将返回的结果。
不必要如果旧数据和当前数据包含在不同的表中，这个非常困难。
查看您的引用，我注意到有一个很大的担忧性能，但没有实际的性能匹配的例子，第一个参考甚至承认我的实践，过滤不活动的行本身并不花费太多。
这符合我对版本化表的测试与数百万个实体的数百万版本的匹配。
通过提供仅暴露的视图来解决复杂性问题当前数据和其他数据暴露整个历史（和其他视图，如有需要）。这对我来说没有问题，因为我倾向于让应用程序访问视图而不是表。
一般的投诉，当你得到它的时候，是使用软删除使数据库开发者的工作更加困难。这是真的。但我们的工作是使用户的工作更容易 - 不是我们自己的。
我的女儿是一家大型银行的金融分析师。她经常告诉我，如果她能够及时回头看看过去某个特定日期的数据，那么她的工作会变得更容易些。当然有档案。但通常情况下，这仅显示数据归档时的最终状态。帐户被打开后，她不能跑步，经历变化，终于结束了。 （这些是大型企业退休账户，而不是定期的支票或储蓄账户。）
但是，她无法让IT部门感兴趣，因为它将太多工作。 悲伤。
I need some wisdom shed on the controversy over soft-deletes vs hard-deletes in a relational-database-centric application. Commentaries like this, this, and this all discourage soft-deletes as basically a hack, a dirty, easy way out, and claim that hard-deletes are the better way.
I'd love to agree. But nobody seems to mention how to deal with one major, inescapable problem: how can one hard-delete a row when a foreign key constraint prevents its deletion, and when cascade-deleting is not an option?
Essentially, if you want to hard-delete entity A, and entity B is pointing to it, and for business reasons B simply cannot be removed, doesn't that force you to use some form of soft-delete (i.e. keep the record right where it is)?
If you have a customer "Acme" who has made transaction 10001, you can't simply just hard-delete Acme because the virtually any business would require that:
- No transaction can be deleted
- A transaction must point to the customer that made it
Regardless of how Acme got "deleted" (hard, soft, over-medium) a sales report, for instance, would still have to show that Transaction #10001 was made on 2010-05-01 by customer Acme--even if Acme is no longer around. So it requires that Acme be still stored at least somewhere in the system.
One thread keeps coming up from the advocates of hard-delete: use a proper audit table, and do things like keep a serialized copy of the deleted object in there. But we can't redirect the FK of Transaction #10001 to this particular row in the audit table. What then? Create an "OldCustomers" table that mirrors the schema of "Customers" and somehow redirect every FK in the system that pointed to Acme there? I'm no DB expert so I don't know if that's even possible, but even if it were, now all your reporting queries have to factor in two tables (sounds even worse than having to append AND IsDeleted = false to all queries)
In short, I feel like I'm missing something because hard- and soft-deletes are debated--implying that they are both feasible options. But as I understand it, in 95% of business cases, there is no possibility for using hard-delete (on a technical level, due FK constraints, and on a logical level, simply because a thing can't just disappear when another thing points to it)
I use a temporal design which make extensive use of soft deletes. Or get the current data by executing the same query "as of" now. The design allows for hard deletes (rarely used), soft deletes (most often used) and firm deletes (like soft deletes but not allowed to undo). This design allows one to execute a query "as of" a date and return the same result that would have been returned if the query had been executed on that date.
Needless to say, this extremely difficult if "old" data and "current" data are contained in different tables.
Looking over your references, I noticed there was a large concern for performance, yet there were no actual examples of performance hits and the first reference even admitted that "[i]n practice, filtering inactive rows doesn’t cost too much in itself."
This matches my testing of "versioned" tables with millions of versions of 100s of thousands of entities.
I get around the complexity issue by supplying views which exposes only current data and others to expose the entire history (and other views as needed). This is no problem for me as I tend to have apps access views instead of tables anyway.
The general complaint, when you get right down to it, is that using soft deletes makes the db developers' jobs more difficult. This is true. But our job is to make the users' jobs easier -- not our own.
My daughter is a financial analyst at a large bank. She often tells me how much easier her job would be if she could look back in time to see what the data looked back at some particular date in the past. There are archives, of course. But typically this only shows the final state of the data when it was archived. She can't run through the progression as accounts are opened, go through changes and finally get closed. (These are large corporate retirement accounts, not regular checking or savings accounts.)
But she can't get her IT dept interested because it would "be too much work." Sad.
这篇关于当涉及到外键时，硬删除工作如何工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
