I'm using SQL Server 2005.
我正在使用SQL Server 2005。
Our application almost never deletes without it being a logical delete and therefore we have no need for cascading deletes.
我们的应用程序几乎从不删除而不是逻辑删除,因此我们不需要级联删除。
In fact, its quite a comfort knowing that the foreign key contraints give us some protection against an accidental delete statement.
事实上,知道外键约束给我们一些保护以防止意外删除声明,这是非常安慰的。
However, very occasionally I need to delete a top level table and all of its children. At the moment I do this with multiple DELETE statements in the write order and it becomes a very large, complex and impossible to keep up to date script.
但是,偶尔我需要删除*表及其所有子项。目前,我在写入顺序中使用多个DELETE语句执行此操作,并且它变得非常大,复杂且无法保持最新脚本。
I'm wondering if there is a way of automatically turning cascading deletes on for all foreign keys in the database, performing my top level delete, and then turning them all back on again?
我想知道是否有一种方法可以自动为数据库中的所有外键启用级联删除,执行我的*删除,然后再将它们重新打开?
3 个解决方案
#1
For another question here, I wrote a script which should generate the deletes automatically.
对于这里的另一个问题,我写了一个脚本,它应该自动生成删除。
#2
How about writing pair of scripts -- one you manually run when you're about to delete records that enables the on delete cascade appropriately for the constraints, and then have another one other that you run once finished deleting to disable them and get things back to normal?
如何编写一对脚本 - 一个是在你要删除的记录时手动运行的,这些记录可以根据约束适当地启用删除级联,然后在完成删除后再运行另一个脚本以禁用它们并将其取回正常吗?
#3
Just a thought: use an INSTEAD OF TRIGGER on the parent table, for deletes, so that all the child-record-delete logic is in one place
只是一个想法:在父表上使用INSTEAD OF TRIGGER进行删除,以便所有子记录删除逻辑都在一个地方
#1
For another question here, I wrote a script which should generate the deletes automatically.
对于这里的另一个问题,我写了一个脚本,它应该自动生成删除。
#2
How about writing pair of scripts -- one you manually run when you're about to delete records that enables the on delete cascade appropriately for the constraints, and then have another one other that you run once finished deleting to disable them and get things back to normal?
如何编写一对脚本 - 一个是在你要删除的记录时手动运行的,这些记录可以根据约束适当地启用删除级联,然后在完成删除后再运行另一个脚本以禁用它们并将其取回正常吗?
#3
Just a thought: use an INSTEAD OF TRIGGER on the parent table, for deletes, so that all the child-record-delete logic is in one place
只是一个想法:在父表上使用INSTEAD OF TRIGGER进行删除,以便所有子记录删除逻辑都在一个地方