DELETE语句违反同表引用约束的SQL问题求助

存储过程代码 #

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_Delete_AssemblyPartListByProjectId]
    (@ProjectId INT,
     @UserId INT = NULL)
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET NOCOUNT ON

    UPDATE AssemblyPartList 
    SET ParentSequence = NULL 
    WHERE ProjectID = @ProjectId

    DELETE FROM AssemblyPartList 
    WHERE ProjectID = @ProjectId
END

执行错误信息 #

Msg 547, Level 16, State 0, Procedure dbo.usp_Delete_AssemblyPartListByProjectId, Line 12
The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_dbo.AssemblyPartList_dbo.AssemblyPartList_AssemblyPartList2_AssemblyPartListID". The conflict occurred in database "db-uat-emd-01", table "dbo.AssemblyPartList", column 'ParentSequence'.

AssemblyPartList表结构 #

CREATE TABLE [dbo].[AssemblyPartList] 
(
    [AssemblyPartListID] INT IDENTITY (1, 1) NOT NULL,
    [PartID]             BIGINT NOT NULL,
    [PartNO]             NVARCHAR(MAX) NULL,
    [OriginalQty]        DECIMAL(18, 2) NOT NULL,
    [AdjustedQty]        DECIMAL(18, 2) NOT NULL,
    [IndentureLevel]     INT NOT NULL,
    [ParentAssemblyID]   BIGINT NOT NULL,
    [Notes]              NVARCHAR(MAX) NULL,
    [Cost]               DECIMAL(18, 2) 
         CONSTRAINT [DF__AssemblyPa__Cost__780AAFAB] DEFAULT ((0)) NOT NULL,
    [ParentAssemblyAssetItemID] INT
         CONSTRAINT [DF__AssemblyP__Paren__79F2F81D] DEFAULT ((0)) NOT NULL,
    [ProjectID]          INT 
         CONSTRAINT [DF__AssemblyP__Proje__2018A105] DEFAULT ((0)) NOT NULL,
    [ParentSequence]     INT NULL,
    [Description]        NVARCHAR(255) NULL,
    [AssetWBSID]         INT             
         CONSTRAINT [DF__AssemblyP__Asset__1407CFDB] DEFAULT ((0)) NOT NULL,
    [AssetItem]          NVARCHAR(100) NULL,
    CONSTRAINT [PK_dbo.AssemblyPartList] 
        PRIMARY KEY CLUSTERED ([AssemblyPartListID] ASC),
    CONSTRAINT [FK_dbo.AssemblyPartList_dbo.AssemblyPartList_AssemblyPartList2_AssemblyPartListID]
        FOREIGN KEY ([ParentSequence]) REFERENCES [dbo].[AssemblyPartList] ([AssemblyPartListID])
);
GO

CREATE NONCLUSTERED INDEX [IX_ParentSequence]
ON [dbo].[AssemblyPartList]([ParentSequence] ASC) WITH (FILLFACTOR = 80);

问题分析与解决 #

报错原因 #

你仅将待删除的目标行的ParentSequence设为NULL，但忽略了表中其他行可能存在ParentSequence指向目标行AssemblyPartListID的情况。自引用外键约束要求：若某行的ParentSequence值非空，必须对应表中已存在的AssemblyPartListID。当删除目标行时，其他行的ParentSequence若指向这些即将被删除的ID，就会触发约束冲突。

解决方法 #

需先将所有引用目标ProjectId下AssemblyPartListID的ParentSequence设为NULL，再执行删除操作。修改后的存储过程如下：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_Delete_AssemblyPartListByProjectId]
    (@ProjectId INT,
     @UserId INT = NULL)
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET NOCOUNT ON;

    -- 清除所有指向当前ProjectId下记录的外部引用
    UPDATE AssemblyPartList
    SET ParentSequence = NULL
    WHERE ParentSequence IN (
        SELECT AssemblyPartListID 
        FROM AssemblyPartList 
        WHERE ProjectID = @ProjectId
    );

    -- 清除当前ProjectId下记录的自引用（可选，后续会删除这些行）
    UPDATE AssemblyPartList 
    SET ParentSequence = NULL 
    WHERE ProjectID = @ProjectId;

    -- 删除目标ProjectId下的所有记录
    DELETE FROM AssemblyPartList 
    WHERE ProjectID = @ProjectId;
END

补充说明 #

如果表中存在层级结构（父项和子项同属一个ProjectId），也可以按层级从最底层（无引用的行）开始删除，再逐步向上清理父项，但上述方法更直接，适合批量清理整个ProjectId下的所有数据。

内容的提问来源于stack exchange，提问作者Oli Ivett