如何从具有外键关系的表中查找正确的序列数据并按顺序删除 [英] How to find proper sequence data and delete in that order from table who is having foreign key relationship

问题描述

我有一个名为product的表。



没有近23个表有关于产品表主键的外键.ie productId



现在这23张桌子也与其他桌子有几个关系。



现在我应该如何正确查询哪些会保留删除所有这些表中的记录都是正确的。



我没有任何删除级联



我尝试过：

I have one table called product.

no there are almost 23 tables who is having foreign keys respect to product tables primary key .ie productId

now those 23 tables also have several relationship with other tables .

now how i should right query which will keep delete records from all these table in correct manner.

I dnt have any on delete cascade

What I have tried:

begin transaction;

   declare @deletedIds table ( productId int );

   insert into @deletedIds
   select p.productId from Product as p 
   where p.ProductID = 131
   
   delete cl 
   from CallOrder as cl
   join @deletedIds as d
   on d.productId = cl.ProductID

   delete cl 
   from CallSample as cl
   join @deletedIds as d
   on d.productId = cl.ProductID



   commit transaction;

现在问题是子表alos与其他表有差异，这就是为什么我无法删除所有这些表中的记录。

now problem is child tables alos have there rference with other table which is why i unable to delete records from all these table .

推荐答案

就我个人而言，我真的不会使用客户端命令。相反，我会将外键的类型更改为DELETE CASCADE，如果逻辑是当产品被删除时，孩子也会被删除。



如果你需要执行附加检查是否可以删除行，例如根据状态或类似情况，然后利用触发器进行此检查并防止不需要的行被删除。



如果您仍想使用该程序删除它们，一种方法可能是递归检查使用的外键，例如 sp_fkeys（Transact-SQL） [ ^ ]。



但是，由于条件发生变化且键值发生变化，您应该考虑所有这些因素，然后遍历树。除非您的程序动态创建表，否则我认为编写静态查询以进行删除更容易。毕竟你可能知道所有可能是产品子项的表。

Personally I really wouldn't do this using client side commands. Instead I would change the types of the foreign keys to DELETE CASCADE if the logic is that when a product is deleted, the children are also deleted.

If you need to perform additional checks whether a row can be deleted or not, for example based on a status or similar, then utilize triggers to make this checks and preventing undesired rows from being deleted.

If you still want to delete them using the program, one way could be to recursively check the foreign keys of the using for example sp_fkeys (Transact-SQL)[^].

However, since the conditions change and the key values change you should take all these into account then traversing the tree. Unless your program dynamically creates tables I believe that it's easier to write static queries to do the deletions. After all you probably know all the tables that can be children of a product.

这篇关于如何从具有外键关系的表中查找正确的序列数据并按顺序删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！