SQL 如何在不删除/重新创建约束的情况下强制 oracle 进行级联删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13881447/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to force oracle to do cascade delete without dropping/recreating constraints
提问by GolezTrol
I've got a parent table, which has a dosen child tables referencing it. Those child tables in turn have other child tables referencing them. And so on.
我有一个父表，其中有一个引用它的子表。这些子表反过来有其他子表引用它们。等等。
I need to delete a row from the main parent table cascading it all the way down.
我需要从主父表中删除一行，将其一直向下级联。
Dropping/recreating constraints all over the place to have them "ON CASCADE DELETE" is not an option
到处删除/重新创建约束以使它们“ON CASCADE DELETE”不是一种选择
Going through all of them and deleting child rows manually is a nightmare.
遍历所有这些并手动删除子行是一场噩梦。
Any other options?
还有其他选择吗？
回答by GolezTrol
The DELETE statement has no parameters to make it cascading.
DELETE 语句没有参数使其级联。
So you can either make user of ON CASCADE DELETEor just execute a bunch of separate deletes in the right order.
因此，您可以ON CASCADE DELETE按正确的顺序使用户使用或仅执行一组单独的删除。
You could write a 'smart delete' procedure which investigates table structure and generates a series of deletes bases on that, but that will likely be more work and more of a nightmare than writing the separate deletes. And you'd haveto have those constraints for this to work, which in reality is not always desired.
您可以编写一个“智能删除”过程，该过程调查表结构并在此基础上生成一系列删除，但这可能比编写单独的删除需要更多的工作，更像是一场噩梦。而且您必须有这些限制才能使其工作，这实际上并不总是需要的。
回答by Helder Velez
I solved this kind of problem in oracle with an application I've done on purpose to merge data from one branch to other one - target, and then delete the source one.
我用一个应用程序解决了 oracle 中的这类问题，我故意将数据从一个分支合并到另一个分支 - 目标，然后删除源分支。
https://sites.google.com/site/keytreechanger/Homehttps://sites.google.com/site/keytreechanger/Home/screenshots
https://sites.google.com/site/keytreechanger/Home https://sites.google.com/site/keytreechanger/Home/screenshots
quoting
引用
Given this error, for example: client Pedrus Root (internal key representation #R=111) is the same as Petrus Root (#R=222). Move data under 222 to 111, and then delete 222 branch.
KTC gathers all data relative to this case (and the next few cases to be solved), in a smarty way from all relevant tables of database.
The algorithm only searches where needed and using all available key components.
A backup of all data is locally maintained for audit purposes. The local repository evolves transparently to accommodate changes in table/field definitions.The power user visually inspects the table/relation tree, and can view/print/edit data in any table and optionally modify the presumed action (cut, or cut and paste or done).
Finally KTC generates hundreds of lines of inserts, updates and deletes of corrective code to solve this particular case, listed in a suitable order ending with
delete from rootTable where #R=222.
Then, I apply the code with the users online.
鉴于此错误，例如：客户端 Pedrus Root（内部密钥表示 #R=111）与 Petrus Root（#R=222）相同。将222下的数据移动到111，然后删除222分支。
KTC 以巧妙的方式从数据库的所有相关表中收集与此案例（以及接下来要解决的几个案例）相关的所有数据。
该算法只搜索需要的地方并使用所有可用的关键组件。
出于审计目的，所有数据的备份都在本地维护。本地存储库透明地发展以适应表/字段定义的变化。高级用户目视检查表/关系树，并可以查看/打印/编辑任何表中的数据，并可选择修改假定的操作（剪切、剪切和粘贴或完成）。
最后，KTC 生成了数百行纠正代码的插入、更新和删除来解决这个特殊情况，以适当的顺序列出，以
从 rootTable 中删除的结尾，其中 #R=222。
然后，我将代码与用户在线应用。
In your situation a filter will have to remove all lines but the delete ones.
在您的情况下，过滤器必须删除所有行，但删除行。
AFAIK there is no equivalent application in the market.
AFAIK 在市场上没有等效的应用程序。
