如何用一条sql删除主明细记录？

Question

使用 Delphi 7 和 interbase 7

是否可以在一个 SQL 语句中删除主详细记录及其所有嵌套的详细记录？

示例：

表1
ID - 整数
标题 - Varchar(80)

表 2
ID - 整数
Table1_ID - 整数
标题 - Varchar(80)

表 3
ID - 整数
Table2_ID - 整数
TITLE - Varchar(80)

我想从 Table1 中删除 ID 10，以及表 2 中的所有匹配记录 (Table1_ID)，以及表 3 中的所有匹配记录 (Table2_ID)

如果我无法在一个 sql 中执行此操作，我如何在多个sql中执行此操作（调用语句的正确顺序）？

Answer 1

你可以通过一些SQL按顺序删除Table3、table2和table1上的记录来完成。一笔交易就像“独特的操作”一样。

一种替代方法是，当您删除表 1 上的一条记录时，使用触发器删除表 2 上的相关记录，并在表 2 中使用触发器来删除表 3 上的相关记录。

另一种（如果数据库允许）是在删除 table1 上的记录时使用 ON CASCADE DELETE （或类似的）删除 tabla2 和 table3 上的相关记录（请参阅有关 SGBD/数据库的帮助或文档）。

抱歉，英语有错误。这不是我的自然语言。

问候。

Answer 2

当父记录被删除时，您可以使用外键将删除级联到子记录。

当删除 Table1 (ID) 中的记录时，以下命令应创建外键以删除 Table2 (Table1_ID) 中的记录。

ALTER TABLE TABLE2 ADD CONSTRAINT FK_TABLE2_TABLE1 
FOREIGN KEY (Table1_ID) REFERENCES TABLE1 (ID) 
ON DELETE CASCADE;

这样，数据库引擎就会处理子表中的删除，因此您所需要做的就是

delete from TABLE1 where ID = :ID_VALUE

问候，

Answer 3

DELETE FROM Table3 WHERE Table2_ID IN (SELECT ID FROM Table_2 WHERE Table1_ID=xxxx)

DELETE FROM Table2 WHERE Table1_ID=xxxx

DELETE FROM Table1 WHERE ID=xxxx

Answer 4

您不能在单个语句中执行此操作，但您可以在 Delphi 中使用事务来确保所有语句都被执行或不执行。

如果您使用BDE，则删除TDatabase组件并设置默认属性并编写以下代码。

try
  Database1.StartTransaction;
  //Execute first query
  //Execute second query
  //Execute third query
  Database1.Commit;
except on E: Exception do
  Database1.Rollback;
end;

如果您使用的是 ADO，则使用 ADOConnection1.BeginTrans; ADOConnection1.CommitTrans; ADOConnection1.RollbackTrans; 语句

Answer 5

如果您使用级联选项创建外键引用，则删除父记录也将删除所有详细信息（除非其他限制阻止这样做）。

SQL：

ALTER TABLE Table2 ADD CONSTRAINT Table2_Table1_ID 
  FOREIGN KEY(Table1_ID) REFERENCES Table1(ID) ON DELETE CASCADE

该解决方案不需要客户端应用程序的配合，服务器将保持数据模型的一致性。

Answer 6

除了已经给出的答案之外，还有多个 SQL 语句、外键关系和触发器。在InterBase中，您还可以编写一个存储过程来删除主记录和明细记录。那么你的程序中只需要一条 SQL 语句。

在这种情况下您可以使用以下两种存储过程。
第一个几乎与 HeartWave 答案相同，但在存储过程中。

CREATE PROCEDURE DELETEMASTERDETAIL_WITHOUTINFO(
    pMasterID INTEGER)
RETURNS (
    rResult INTEGER)
AS
declare variable vTable2ID integer;
begin
  /* don't return information about deleted records */
  rResult = 0;

  for select id
      from table2
      where table1_id = :pMasterID
      into :vTable2ID do
  begin
    delete from table3
    where table2_id = :vTable2ID;
  end

  delete from table2
  where table1_id = :pMasterID;

  delete from table1
  where id = :pMasterID;

  rResult = rResult + 1;

  suspend;
end

调用该存储过程的 SQL 语句是：

select rresult
from deletemasterdetail_withoutinfo(:pMasterID)

第二个语句将返回有关每个表已删除记录数量的信息。我不知道你是否需要它，但也许对其他人有帮助。如果 Table1 中的 ID 字段是主键，那么第一个 for select 语句就有点大材小用了。

CREATE PROCEDURE DELETEMASTERDETAIL_WITHINFO(
    pMasterID INTEGER)
RETURNS (
    rTable1Deleted INTEGER,
    rTable2Deleted INTEGER,
    rTable3Deleted INTEGER)
AS
declare variable vTable1ID integer;
declare variable vTable2ID integer;
declare variable vTable3ID integer;
begin
  /* return information about deleted records */
  rTable1Deleted = 0;
  rTable2Deleted = 0;
  rTable3Deleted = 0;

  for select id
      from table1
      where id = :pMasterID
      into :vTable1ID do
  begin
    for select id
        from table2
        where table1_id = :vTable1ID
        into :vTable2ID do
    begin
      for select id
          from table3
          where table2_id = :vTable2ID
          into :vTable3ID do
      begin
        rTable3Deleted = rTable3Deleted + 1;

        delete from table3
        where id = :vTable3ID;
      end

      rTable2Deleted = rTable2Deleted + 1;
      delete from table2
      where id = :vTable2ID;
    end 

    rTable1Deleted = rTable1Deleted + 1;
    delete from table1
    where id = :vTable1ID;
  end

  suspend;
end

调用该存储过程的 SQL 语句是：

select rtable1deleted, rtable2deleted, rtable3deleted
from deletemasterdetail_withinfo(:pMasterID)

BTW。我几乎总是在 SP 中使用至少一个返回参数。这将允许使用查询组件来调用存储过程。
如果没有结果参数，则必须使用存储过程组件来执行 SP。