Vaqtinchalik yoki so'rovni qanday qilib o'chirish mumkin?

Ba'zan MSSQL ichida faqat bitta satrni o'chirishga harakat qilaman va xorijiy kalit cheklovlari tufayli murojaatlar tufayli ierarxiyani son-sanoqsiz o'chirib tashlayman. Chek-tugma cheklovlarini kaskadni o'chirib tashlashni o'rnatmasdan avtomatik ravishda kaskaddan o'chirish uchun tezkor usul bormi? Faqat bir marta, kaskadni yo'q qilishni talab qilaman ... on-demand - har doim emas.

Har qanday imkoniyat bormi? Har qanday ekvivalentlar?

1
@GordonLinoff - Men savolni noto'g'ri tushuntirdim. Tahririmdan keyin uni yana o'qing. Shunga qaramay, SqlZim, barchasini ochib berish uchun.
qo'shib qo'ydi muallif BeemerGuy, manba
@GordonLinoff - Men savolni noto'g'ri tushuntirdim. Tahririmdan keyin uni yana o'qing. Shunga qaramay, SqlZim, barchasini ochib berish uchun.
qo'shib qo'ydi muallif BeemerGuy, manba
@GordonLinoff - Men savolni noto'g'ri tushuntirdim. Tahririmdan keyin uni yana o'qing. Shunga qaramay, SqlZim, barchasini ochib berish uchun.
qo'shib qo'ydi muallif BeemerGuy, manba
Chekki kalit cheklovlari aniqlanmagan bo'lsa, ma'lumotlar bazasi kaskad qanday o'chirishi mumkin? Ustunlar nima bilan bog'liqligini bilishmaydi.
qo'shib qo'ydi muallif Gordon Linoff, manba
Chekki kalit cheklovlari aniqlanmagan bo'lsa, ma'lumotlar bazasi kaskad qanday o'chirishi mumkin? Ustunlar nima bilan bog'liqligini bilishmaydi.
qo'shib qo'ydi muallif Gordon Linoff, manba
Chekki kalit cheklovlari aniqlanmagan bo'lsa, ma'lumotlar bazasi kaskad qanday o'chirishi mumkin? Ustunlar nima bilan bog'liqligini bilishmaydi.
qo'shib qo'ydi muallif Gordon Linoff, manba
@GordonLinoff Men chet ellik kalit cheklovlar mavjud deb o'ylayman (shuning uchun u istagan qatorni yo'q qila olmaydi), faqat kaskadni yo'q qilish deb belgilanmaydi.
qo'shib qo'ydi muallif SqlZim, manba
@GordonLinoff Men chet ellik kalit cheklovlar mavjud deb o'ylayman (shuning uchun u istagan qatorni yo'q qila olmaydi), faqat kaskadni yo'q qilish deb belgilanmaydi.
qo'shib qo'ydi muallif SqlZim, manba
@GordonLinoff Men chet ellik kalit cheklovlar mavjud deb o'ylayman (shuning uchun u istagan qatorni yo'q qila olmaydi), faqat kaskadni yo'q qilish deb belgilanmaydi.
qo'shib qo'ydi muallif SqlZim, manba

6 javoblar

Bir nuqtani istasangiz va dinamik SQL echimini olsangiz, bu maxsus kalitdan tushadigan chet el kalitlari uchun jadval hiyerarşisi yaratish uchun o'z-o'zidan bir so'rovni ishlatadi. Shu bilan birga jadvalda ma'lum bir qatorni yo'q qilish uchun (umid qilamanki) qatl qilinishi kerak bo'lgan o'chirish so'zlarini ishlab chiqaradi.

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)+' = '[email protected]+';'
  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;

Yaratilgan kod:

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;
1
qo'shib qo'ydi
Rahmat! Bu juda chiroyli. Buni o'zida saqlab turadigan usul sifatida bajarish mumkin va jadval nomi va asosiy kalit qiymatini parametrlar sifatida berishi mumkin - qolganlarini o'z-o'zidan tushunishi mumkin. Ajoyib!
qo'shib qo'ydi muallif BeemerGuy, manba
@BeemerGuy yordam berishdan baxtiyor!
qo'shib qo'ydi muallif SqlZim, manba

Bir nuqtani istasangiz va dinamik SQL echimini olsangiz, bu maxsus kalitdan tushadigan chet el kalitlari uchun jadval hiyerarşisi yaratish uchun o'z-o'zidan bir so'rovni ishlatadi. Shu bilan birga jadvalda ma'lum bir qatorni yo'q qilish uchun (umid qilamanki) qatl qilinishi kerak bo'lgan o'chirish so'zlarini ishlab chiqaradi.

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)+' = '[email protected]+';'
  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;

Yaratilgan kod:

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;
1
qo'shib qo'ydi
Rahmat! Bu juda chiroyli. Buni o'zida saqlab turadigan usul sifatida bajarish mumkin va jadval nomi va asosiy kalit qiymatini parametrlar sifatida berishi mumkin - qolganlarini o'z-o'zidan tushunishi mumkin. Ajoyib!
qo'shib qo'ydi muallif BeemerGuy, manba
@BeemerGuy yordam berishdan baxtiyor!
qo'shib qo'ydi muallif SqlZim, manba

Bir nuqtani istasangiz va dinamik SQL echimini olsangiz, bu maxsus kalitdan tushadigan chet el kalitlari uchun jadval hiyerarşisi yaratish uchun o'z-o'zidan bir so'rovni ishlatadi. Shu bilan birga jadvalda ma'lum bir qatorni yo'q qilish uchun (umid qilamanki) qatl qilinishi kerak bo'lgan o'chirish so'zlarini ishlab chiqaradi.

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)+' = '[email protected]+';'
  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;

Yaratilgan kod:

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;
1
qo'shib qo'ydi
Rahmat! Bu juda chiroyli. Buni o'zida saqlab turadigan usul sifatida bajarish mumkin va jadval nomi va asosiy kalit qiymatini parametrlar sifatida berishi mumkin - qolganlarini o'z-o'zidan tushunishi mumkin. Ajoyib!
qo'shib qo'ydi muallif BeemerGuy, manba
@BeemerGuy yordam berishdan baxtiyor!
qo'shib qo'ydi muallif SqlZim, manba

Sizga bir martalik maqsadlar kerak bo'lsa-da, lekin uni doimo taqdim qilishni istamaysiz, menimcha saqlanadigan procni yozish uchun eng yaxshi deb o'ylayman.

Avvalo, eng avvalo stoldan o'chirib tashlang va daraxtni qayta tiklang

Mana bir misol:

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
0
qo'shib qo'ydi

Sizga bir martalik maqsadlar kerak bo'lsa-da, lekin uni doimo taqdim qilishni istamaysiz, menimcha saqlanadigan procni yozish uchun eng yaxshi deb o'ylayman.

Avvalo, eng avvalo stoldan o'chirib tashlang va daraxtni qayta tiklang

Mana bir misol:

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
0
qo'shib qo'ydi

Sizga bir martalik maqsadlar kerak bo'lsa-da, lekin uni doimo taqdim qilishni istamaysiz, menimcha saqlanadigan procni yozish uchun eng yaxshi deb o'ylayman.

Avvalo, eng avvalo stoldan o'chirib tashlang va daraxtni qayta tiklang

Mana bir misol:

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
0
qo'shib qo'ydi