从两个表中删除行 [英] Delete rows from two tables

问题描述

我有两个桌子。这些表之间有两个关系。

I have two tables. Those tables have two relation between them.

Table 1
   * ID_XPTO (PK)
   * Detail

Table 2
   * ID_XPTO (FK) (PK)
   * ID_XPTO2 (FK) (PK)

存在两个关系。

Table 1 -< Table2 
Table 1 -< Table2

我的问题是我需要删除表1中的某些行。

My question is that I need to delete some row in table 1. I'm currently doing,

declare @table Table (xptoTable2 int)
insert into @table
        select ID_XPTO2
        from Table2 
        where ID_XPTO = @ID_XPTO

delete from Table2
where ID_XPTO = @ID_XPTO

delete from Table
where ID_XPTO in (select xptoTable2from @table)

我知道我可以在table2上使用ON DELETE SET NULL。这样，我就可以在ID_XPTO2上搜索所有具有空值的行并将其删除，但是DBA不想使用它。

I know that I could use ON DELETE SET NULL on table2. On that way I could then search for all rows with null value on ID_XPTO2 and delete them, but DBA does not wants to use it.

有没有更好的解决方案

推荐答案

您有以下选择：

• 像现在一样，删除两个语句。首先从Table2中删除。

• Delete in two statements, as you are doing now. Delete from Table2 first.

如果您的数据库品牌支持多表<$ c $ DELETE 语法（例如MySQL）。这不是标准的SQL，但它很方便。

Delete from two tables in one statement, if your brand of database supports multi-table DELETE syntax (e.g. MySQL). This is not standard SQL, but it is handy.

使用级联参照完整性约束（我知道您的DBA已经取消了此选项）。

Use cascading referential integrity constraints (I understand your DBA has nixed this option).

在表1上编写触发器删除前，以删除或将表2中的任何引用设置为NULL。请咨询您的DBA，以查看是否比级联的RI约束更可接受。

Write a trigger BEFORE DELETE on Table1, to delete or set NULL any reference in Table2. Check with your DBA to see if this is any more acceptable than the cascading RI constraints.

最后，我建议与您的DBA交谈并询问您在此处提出的相同问题。找出他/她希望您使用的解决方案。 StackOverflow上的人们可以回答技术问题，但是听起来您正在处理 IT政策问题。

Finally, I would advise talking to your DBA and asking the same question you asked here. Find out what solution he/she would prefer you to use. Folks on StackOverflow can answer technical questions, but it sounds like you are dealing with an IT policy question.

这篇关于从两个表中删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！