SQL 如何删除表中包含指向其他表的外键的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3656099/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete rows in tables that contain foreign keys to other tables
提问by
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.
假设有一个包含主键的主表，还有另一个包含该主表的外键的表。因此，如果我们删除主表的行，它也会删除子表。
How do I write this query?
如何编写此查询？
回答by Lieven Keersmaekers
From your question, I think it is safe to assume you have CASCADING DELETESturned on.
All that is needed in that case is
从您的问题来看，我认为可以安全地假设您已打开CASCADING DELETES。
在这种情况下所需要的只是
DELETE FROM MainTable
WHERE PrimaryKey = ???
You database engine will take care of deleting the corresponding referencing records.
您的数据库引擎将负责删除相应的引用记录。
回答by onedaywhen
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
);
Second, as a one-time schema-alteration exercise, add the ON DELETE CASCADEreferential action to the foreign key on the referencing table e.g.
其次，作为一次性模式更改练习，将ON DELETE CASCADE引用操作添加到引用表上的外键，例如
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.
然后，当引用表中的行被删除时，引用表中的行将永远被删除。
回答by Anil Soman
You can alter a foreign key constraint with delete cascade option as shown below. This will delete chind table rows related to master table rows when deleted.
您可以使用删除级联选项更改外键约束，如下所示。这将在删除时删除与主表行相关的 chind 表行。
ALTER TABLE MasterTable
ADD CONSTRAINT fk_xyz
FOREIGN KEY (xyz)
REFERENCES ChildTable (xyz) ON DELETE CASCADE
回答by Milena
If you have multiply rows to delete and you don't want to alter the structure of your tables you can use cursor. 1-You first need to select rows to delete(in a cursor) 2-Then for each row in the cursor you delete the referencing rows and after that delete the row him self.
如果要删除多行并且不想更改表的结构，则可以使用游标。1-您首先需要选择要删除的行（在游标中） 2-然后对于游标中的每一行，您删除引用行，然后自己删除该行。
Ex:
前任：
--id is primary key of MainTable
declare @id int
set @id = 1
declare theMain cursor for select FK from MainTable where MainID = @id
declare @fk_Id int
open theMain
fetch next from theMain into @fk_Id
while @@fetch_status=0
begin
--fkid is the foreign key
--Must delete from Main Table first then child.
delete from MainTable where fkid = @fk_Id
delete from ReferencingTable where fkid = @fk_Id
fetch next from theMain into @fk_Id
end
close theMain
deallocate theMain
hope is useful
希望有用
回答by Deepu Surendran
Need to set the foreign key option as on delete cascade... in tables which contains foreign key columns.... It need to set at the time of table creation or add later using ALTER table
需要将外键选项设置为删除级联...在包含外键列的表中...。需要在创建表时设置或稍后使用ALTER表添加
