Как каскадно-удалить временно или по запросу?

Question

Иногда я пытаюсь удалить только одну строку в MSSQL, и я попадаю в бесчисленное количество удалений вверх по иерархии из-за ссылок из-за ограничений внешнего ключа. Есть ли какой-нибудь быстрый способ автоматического каскадного удаления без необходимости настройки ограничений внешнего ключа с помощью каскадного удаления? Только в этот раз мне нужно каскадное удаление... по требованию - не всегда.

Любой шанс? Есть эквиваленты?

Answer 1

Если вы хотите использовать динамическое sql-решение "наведи и снимай", он использует рекурсивный запрос для построения иерархии таблиц для внешних ключей, которая происходит от конкретного ключа. При этом он генерирует операторы delete, которые необходимо выполнить, чтобы (надеюсь) удалить конкретную строку из таблицы.

use AdventureWorks2012

declare @tablename sysname = N'Production.Product';
declare @primarykeycolumn sysname = N'ProductId';
declare @value nvarchar(128) = '2';
declare @sql nvarchar(max);

;with tableHierarchy as (
select
    object_id = p.object_id
  , parent_id = cast(null as int)
  , schemaName = schema_name(p.schema_id)
  , tableName = object_name(p.object_id)
  , parentObjectName = cast(null as sysname)
  , parentToChild = cast(object_name(p.object_id) as varchar(max))
  , childToParent = cast(object_name(p.object_id) as varchar(max))
  , treelevel = 0
  , keyName = p.name
  , columnName = c.name
  , columnId = c.column_id
  , parentColumnName = c.name
from sys.objects as p
  inner join sys.columns c
    on p.object_id = c.object_id
where p.object_id  = object_id(@tablename)
  and c.name = @primarykeycolumn
union all
select
    object_id = fk.parent_object_id
  , parent_id = fk.referenced_object_id
  , schemaName = schema_name(fk.schema_id)
  , tableName = object_name(fk.parent_object_id)
  , parentObjectName = object_name(fk.referenced_object_id)
  , parentToChild = parentToChild + ' \ ' + cast(object_name(fk.parent_object_id) as varchar(128))
  , childToParent = cast(object_name(fk.parent_object_id) as varchar(128)) + ' \ ' + childToParent
  , treelevel = th.treelevel + 1
  , keyName = fk.name
  , columnName = c.name
  , columnId = c.column_id
  , parentColumnName = rc.name
from tableHierarchy as th
  inner join sys.foreign_keys as fk
    on fk.referenced_object_id = th.object_id
   and fk.referenced_object_id != fk.parent_object_id 
  inner join sys.foreign_key_columns fkc
    on fk.object_id = fkc.constraint_object_id
  and fkc.referenced_column_id = th.columnId
  inner join sys.columns c
    on fkc.parent_object_id = c.object_id
   and fkc.parent_column_id = c.column_id
  inner join sys.columns rc
    on fkc.referenced_object_id = rc.object_id
   and fkc.referenced_column_id = rc.column_id
)
select @sql = stuff((
  select 
      char(10)
    --+'/* treelevel: '+convert(nvarchar(10),treelevel)
    --+' | ' + childtoparent +' */'+char(10)
    +'delete from '+quotename(schemaName)+'.'+quotename(tableName)
    +' where '+quotename(columnName)+' = '+@value+';'
  from tableHierarchy
  group by treelevel, childtoparent, schemaName, tableName, columnName
  order by treelevel desc, childtoparent
  for xml path (''), type).value('.','nvarchar(max)')
  ,1,1,'')
  option ( maxrecursion 100 );

select @sql as CodeGenerated;
--exec sp_executesql @sql;

Сгенерированный код:

delete from [Sales].[SalesOrderDetail] where [ProductID] = 2;
delete from [Production].[BillOfMaterials] where [ComponentID] = 2;
delete from [Production].[BillOfMaterials] where [ProductAssemblyID] = 2;
delete from [Production].[ProductCostHistory] where [ProductID] = 2;
delete from [Production].[ProductDocument] where [ProductID] = 2;
delete from [Production].[ProductInventory] where [ProductID] = 2;
delete from [Production].[ProductListPriceHistory] where [ProductID] = 2;
delete from [Production].[ProductProductPhoto] where [ProductID] = 2;
delete from [Production].[ProductReview] where [ProductID] = 2;
delete from [Purchasing].[ProductVendor] where [ProductID] = 2;
delete from [Purchasing].[PurchaseOrderDetail] where [ProductID] = 2;
delete from [Sales].[ShoppingCartItem] where [ProductID] = 2;
delete from [Sales].[SpecialOfferProduct] where [ProductID] = 2;
delete from [Production].[TransactionHistory] where [ProductID] = 2;
delete from [Production].[WorkOrder] where [ProductID] = 2;
delete from [Production].[Product] where [ProductID] = 2;

Answer 2

Так как он нужен вам для одноразовых целей, но вы не хотите, чтобы он присутствовал всегда, я думаю, вам лучше всего написать сохраненный процесс.

Просто удалите со стола, который находится дальше всего, и вернитесь к дереву

Вот пример:

Create Proc CascaseDeleteMyTable
    @MyTableId Int
As


Delete From ChildTable33 Where ChildParent33Id In (Select ChildParent33Id From ChildParent33 Where MyTableId = @MyTableId)

Delete From ChildTable2 Where MyTableId = @MyTableId

GO