担心删除带有外键的行时的递归
我正在尝试清理一个包含大量垃圾记录的数据库，
它是一个关系结构，并且有外键将所有内容链接在一起，我设置了一个删除命令：
DELETE FROM Members
WHERE (CurrentClass = 339) AND (YEAR(LastSessionDate) < 2011)
数据库结构如下所示
Members
-----------------------------------------------------------------------
Id ¦ FirstName ¦ LastName ¦ JoiningClass ¦ CurrentClass ¦ CurrentScheme
-----------------------------------------------------------------------
0 ¦ Fakey ¦ McFake ¦ 123 ¦ 999 ¦ 2
-----------------------------------------------------------------------
1 ¦ Fakette ¦ McFake ¦ 123 ¦ 998 ¦ 1
-----------------------------------------------------------------------
：表在JoiningClass、CurrentClass 和CurrentScheme 字段上引用表Classes 和Pricing，这些表然后使用它们保存的字段引用其他表，所以基本上所有内容都引用一切。
我想删除符合我的条件的上述记录，但我收到外键警告。
我曾考虑添加：
ON DELETE CASCADE
但我担心由于所有内容都引用其他所有内容，因此它会删除所有数据库数据。
IE 如果我删除上面的记录 0，它将删除类 123 和 999 ，进而删除其所有引用。
这种情况会发生吗还是我应该更清楚？
I am trying to clean up a database with a load of crap records in it,
It is of a relational structure and there are foreign keys linking everything together, I have got a delete command set up:
DELETE FROM Members
WHERE (CurrentClass = 339) AND (YEAR(LastSessionDate) < 2011)
The DB structure looks like this:
Members
-----------------------------------------------------------------------
Id ¦ FirstName ¦ LastName ¦ JoiningClass ¦ CurrentClass ¦ CurrentScheme
-----------------------------------------------------------------------
0 ¦ Fakey ¦ McFake ¦ 123 ¦ 999 ¦ 2
-----------------------------------------------------------------------
1 ¦ Fakette ¦ McFake ¦ 123 ¦ 998 ¦ 1
-----------------------------------------------------------------------
The above table references tables Classes and Pricing on the fields JoiningClass, CurrentClass and CurrentScheme those tables then reference other tables with fields they hold, so basically everything references everything.
I want to delete the above records that match criteria of mine but I get a foreign key warning.
I had considered adding:
ON DELETE CASCADE
To it but I am afraid as everything references everything else it will delete ALL of the database data.
I.E. If I delete record 0 above that it will delete the classes 123 and 999 and that will in turn delete all of its references.
Does this happen or should I know better?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果正如您所说的“一切都引用一切”，那么如果不对模式进行一些认真的（阅读：耗时的，烦人的）分析，就很难准确地判断会发生什么。使用第 3 方工具也是如此 - 您永远无法 100% 确定它们到底会做什么，并且您不想在生产数据库上冒险删除记录。
相反，您应该创建数据库的另一个副本（备份架构，然后从另一台服务器上的备份重新创建它），添加级联触发器并看看会发生什么。
If "everything references everything" as you say, then it's hard to tell what would happen exactly without some serious (read: time consuming, annoying) analysis of the schema. The same goes for using 3rd party tools - you're never 100% sure what they'll do exactly, and deleting records isn't something you want to risk on a production database.
Instead, you should create another copy of your database (backup your schema, then recreate it from backup on another server), add your cascading triggers and see what happens.
我猜想 JoiningClass 和 CurrentClass 是 Members 记录的父级。因此它们不会被删除。当然，您收到的消息表明它是一个包含子记录的不同表，导致删除失败。现在只有您知道这是否是正确的行为，即如果有子记录您不希望删除这些记录，或者您是否想通过级联删除来控制所有删除。就我个人而言，我从不使用级联删除，因为如果它们有很多子记录（在这种情况下最好先批量删除它们，然后再删除父表），它可能会导致性能问题，或者您遇到上面的情况，您在任何情况下都不想要如果子项存在则删除。例如，如果某个客户有过去的订单，而您需要提供正确的财务报告，那么您不想删除该客户，在这种情况下，您可以使该客户处于非活动状态而不是删除。
在尝试使用级联删除进行任何删除之前，请确保您拥有当前备份。
I would guess that JoiningClass and CurrentClass are parents of the Members record. Therefore they won't get deleted. Certainly the message you are getting indicates that it is a different table that has child records which is causing the delete to fail. Now only you can know if that is the correct behavior, i.e. if there are child records you do not want the records to be deleted or if you want to control all deleting through cascade delete. Personally I never use cascade delete becasue it can cause performance issues if they are many child records (in which case it is best to delete them in batches first then the parent table) or you have the case above where you do not under any circumstances want to delete if the child exists. For instance you would not want to delete a customer if they have a past order that you need to have correct financial reporting, in this case you make the customer inactive rather than deleting.
Make sure you have current backups before trying any deleting with cascade delete.