如何删除表中包含指向其他表的外键的行 [英] How to delete rows in tables that contain foreign keys to other tables

问题描述

假设有一个包含主键的主表，还有另一个包含该主表的外键的表.因此，如果我们删除主表的行，它也会删除子表.

Suppose there is a main table containing a primary key and there is another table which contains a foreign key to this main table. So if we delete the row of main table it will delete the child table also.

如何编写此查询?

推荐答案

首先，作为一次性数据清理练习，删除孤立的行，例如

First, as a one-time data-scrubbing exercise, delete the orphaned rows e.g.

DELETE 
  FROM ReferencingTable 
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM MainTable AS T1
                    WHERE T1.pk_col_1 = ReferencingTable.pk_col_1
                  );

其次，作为一次性模式更改练习，将 ON DELETE CASCADE 引用操作添加到引用表上的外键，例如

Second, as a one-time schema-alteration exercise, add the ON DELETE CASCADE referential action to the foreign key on the referencing table e.g.

ALTER TABLE ReferencingTable DROP 
   CONSTRAINT fk__ReferencingTable__MainTable;

ALTER TABLE ReferencingTable ADD 
   CONSTRAINT fk__ReferencingTable__MainTable 
      FOREIGN KEY (pk_col_1)
      REFERENCES MainTable (pk_col_1)
      ON DELETE CASCADE;

然后，当引用表中的行被删除时，引用表中的行将永远被删除.

Then, forevermore, rows in the referencing tables will automatically be deleted when their referenced row is deleted.

这篇关于如何删除表中包含指向其他表的外键的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！