时间戳

首页 > 解决方案 > SQL 只删除不与外部约束冲突的记录

tsql - SQL 只删除不与外部约束冲突的记录

问题描述

如何通过 FK 从表中删除未被其他表中的任何记录引用的记录？

我正在使用 SQL Server 2014。示例：

CREATE TABLE Persons (
    PersonID int NOT NULL PRIMARY KEY,
    PersonName varchar(20) NOT NULL);
INSERT INTO Persons VALUES (1, 'Alistair'), (2, 'Bob'), (3, 'Cecile')

CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID));
INSERT INTO Orders values (1), (1), (3)

DELETE FROM Persons //-->The DELETE statement conflicted with the REFERENCE constraint FK__Orders__PersonID...

我只想从 Persons 中删除Bob（因为没有属于他的订单）并保留Alistair和Cecile（当然也没有错误消息）。

动机：我需要这个来删除过时或不需要的记录的维护工作。我能想到的解决方案DELETE FROM Persons WHERE NOT EXISTS(SELECT 1 FROM Orders WHERE Orders.PersonID = Persons.PersonID) 不是很好，因为在我的真实数据库中有很多表和 FK，而且如果有人引入另一个 FK，我的代码就会中断。所以寻找更通用的解决方案。

更新：只是为了扩展为什么我正在寻找另一种解决方案，而不是DELETE ... WHERE NOT EXISTS我的数据库中有多个表，我需要在 WHERE 子句中手动列出所有这些表DELETE ... WHERE NOT EXISTS(...Table1...) AND NOT EXISTS(...Table2...) ... AND NOT EXISTS(...TableN...)，如果开发人员添加TableN+1，我的代码也需要更改。所以我希望有一些 SQL 命令/选项来做我想要的：只删除不冲突的记录，而不需要手动列出 SQL 引擎已经知道的所有约束。

标签： tsqlforeign-keyssql-delete

解决方案

一种方法是使用EXISTS：

DELETE P
FROM Persons P
WHERE NOT EXISTS(SELECT 1
                 FROM dbo.Orders O
                 WHERE O.PersonID = P.PersonID);

另一个是使用LEFT JOIN：

DELETE P
FROM Persons P
     LEFT JOIN dbo.Orders O ON P.PersonID = O.PersonID
WHERE O.OrderID IS NULL;

推荐阅读