在已创建表的删除级联上 [英] on delete cascade for already created table

问题描述

嗨...........

我正在尝试从具有外键引用的主键表中删除记录.我想先从外键表中删除记录，然后再从主键中删除.我知道在删除级联上是可能的，但我想在删除级联上应用现有表而不更改table.I已知通过删除foregn键ref并在delete级联上重新定义它是可能的，但是我想要其他选项.是否有任何sql查询，我可以通过它对自身的查询中的delete级联应用. Thanx

hi...........

I am trying to delete record from primary key table which having foreign key reference.i want delete record first from foreign key table then primary key.i know by on delete cascade it is possible.but i am want to apply this on delete cascade on existing table without altering table.I known by droping foregn key ref and redefining with on delete cascade it is possible but i want other option .is there any sql query by which i can apply on delete cascade within query it self.
Thanx

推荐答案

您最好的选择就是您已经拒绝的选择:删除级联.这样可以确保具有外键的表始终与主表保持最新.但是，如果您不能修改该表，则下一个最佳选择是添加一个触发器.这是另一种类型的修改，可能是您可以做的.您将在DELETE命令上执行而不是触发器.在删除之前，您需要从外键表中删除与要删除的行相对应的所有记录.

如果这两个选项都不可接受，并且您必须在查询中执行此操作，则将需要对要删除的行执行SELECT，然后删除外键表中匹配的项.完成此操作后，您就可以完成对主键表的实际删除.看起来像这样(伪代码):

Your best bet is the one you already rejected: delete cascading. That ensures that the table that has the foreign key is always up to date with the primary table. However, if you cannot modify this table, your next best option would be to add a trigger. This is a different type of modification and it might be something that you could do. You would do an Instead Of trigger on the DELETE command. Before the delete, you would then delete all of the records from the foreign key table that correspond to the row being deleted.

If neither of these options are acceptable and you must do it in your query, you are going to need to do a SELECT on the rows you want to delete and then delete the items in the foreign key table that match. Once that is done, you can finish with the actual delete of the primary key table. It would look something like this (pseudo-code):

DELETE 
FROM ForeignKeyTable
WHERE LinkingID IN (SELECT * FROM PrimaryKeyTable WHERE <criteria to="" find="" rows="" be="" deleted="">);

DELETE 
FROM PrimaryKeyTable
WHERE <criteria to="" find="" rows="" be="" deleted=""></criteria></criteria>

这两个语句一起工作，将删除引用主键表的所有行，然后也将删除主键表中的行.

Those two statements, working together, will delete all of the rows that reference the primary key table and then they will delete the rows in the primary key table as well.

这篇关于在已创建表的删除级联上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！