在SQL Server表中使用外键删除数据

I'm going to delete data in an SQL Server table (parent) which has a relationship with another table (child).
I tried the basic Delete query. But it isn't working (and I know it won't).

我要删除与另一个表（子）有关系的SQL Server表（父）中的数据。我尝试了基本的删除查询。但它不起作用（我知道它不会）。

DELETE FROM table WHERE ...

It returned following error

它返回以下错误

The DELETE statement conflicted with the REFERENCE constraint ...

DELETE语句与REFERENCE约束冲突...

I need to keep the table's schema. I know that I just need to add some words in the query, I've ever done this before, but I just couldn't recall it.

我需要保留表的架构。我知道我只需要在查询中添加一些单词，我之前就已经这样做了，但我无法回想起来。

7 个解决方案

#1

You can disable and re-enable the foreign key constraints before and after deleting:

您可以在删除之前和之后禁用并重新启用外键约束：

alter table MyOtherTable nocheck constraint all
delete from MyTable
alter table MyOtherTable check constraint all

#2

You need to manually delete the children. the <condition> is the same for both queries.

您需要手动删除子项。两个查询的相同。

DELETE FROM child
FROM cTable AS child
INNER JOIN table AS parent ON child.ParentId = parent.ParentId
WHERE <condition>;

DELETE FROM parent
FROM table AS parent
WHERE <condition>;

#3

If you wish the delete to be automatic, you need to change your schema so that the foreign key constraint is ON DELETE CASCADE.

如果希望删除是自动的，则需要更改架构，以便外键约束为ON DELETE CASCADE。

For more information, see the MSDN page on Cascading Referential Integrity Constraints.

有关更多信息，请参阅级联参照完整性约束的MSDN页面。

ETA (after clarification from the poster): If you can't update the schema, you have to manually DELETE the affected child records first.

ETA（在海报澄清之后）：如果无法更新架构，则必须先手动删除受影响的子记录。

#4

here you are adding the foreign key for your "Child" table

在这里，您要为“Child”表添加外键

ALTER TABLE child
ADD FOREIGN KEY (P_Id)
REFERENCES parent(P_Id) 
ON DELETE CASCADE
ON UPDATE CASCADE;

After that if you make a DELETE query on "Parent" table like this

之后，如果您在“父”表上进行DELETE查询，就像这样

DELETE FROM parent WHERE .....

since the child has a reference to parent with DELETE CASCADE, the "Child" rows also will be deleted! along with the "parent".

由于子项具有DELETE CASCADE对父项的引用，因此“Child”行也将被删除！和“父母”一起。

#5

So, you need to DELETE related rows from conflicted tables or more logical to UPDATE their FOREIGN KEY column to reference other PRIMARY KEY's from the parent table.

因此，您需要从冲突的表中删除相关的行或更符合逻辑，以更新其FOREIGN KEY列以引用父表中的其他PRIMARY KEY。

Also, you may want to read this article Don’t Delete – Just Don’t

此外，您可能希望阅读本文不要删除 - 只是不要

#6

To delete data from the tables having relationship of parent_child, First you have to delete the data from the child table by mentioning join then simply delete the data from the parent table, example is given below:

要从具有parent_child关系的表中删除数据，首先必须通过提及连接从子表中删除数据，然后只需从父表中删除数据，示例如下：

DELETE ChildTable
FROM ChildTable inner join ChildTable on PParentTable.ID=ChildTable.ParentTableID
WHERE <WHERE CONDITION> 


DELETE  ParentTable
WHERE <WHERE CONDITION>

#7

Usefull script which you can delete all data in all tables of a database , replace tt with you databse name :

您可以删除数据库的所有表中的所有数据的有用脚本，用您的数据库名称替换tt：

declare @tablename nvarchar(100)
declare c1 cursor for
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG='tt' AND TABLE_TYPE='BASE TABLE'

open  c1
fetch next from c1 into @tablename

while @@FETCH_STATUS = 0
    begin
    print @t1
        exec('alter table ' + @tablename + ' nocheck constraint all')
        exec('delete from ' + @tablename)
        exec ('alter table ' + @tablename + ' check constraint all')
        fetch next from c1 into @tablename
    end
close c1
DEALLOCATE c1

#1