代码之家 › 专栏 › 技术社区 › Oli Ivett

DELETE语句与SAME TABLE REFERENCE约束冲突。SQL问题

entity-framework sql-server sql c#

Oli Ivett · 技术社区 · 2 年前

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
GO

消息547,级别16,状态0,过程dbo.usp_Delte_AssemblyPartListByProjectId,第12行 DELETE语句与SAME TABLE REFERENCE约束“FK_dbo.AssemblyPartList_dbo.AassemblyPartList_AssemblyPartList2_AssemblyPartListID”冲突。冲突发生在数据库“db-uat-emd-01”、表“dbo.AssemblyPartList”、列“ParentSequence”中。

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);

我无法使用存储过程从表中删除项目数据来删除行。不确定原因,因为它为null,但仍有引用错误。应用程序中用于从生成的表中清除数据的存储过程。

1 回复 | 直到 2 年前

Charlieface 2 年前

问题是您有一个自引用外键,它阻止您删除此行,因为有依赖于它的子行 UPDATE 语句并没有帮助,因为它只会影响父行,这是您需要担心的依赖行。

您可以使用递归CTE来查找要删除的所有行(以及递归所有子行)。只要您一次将它们全部删除,服务器就会确保它们以正确的顺序全部删除。

CREATE OR ALTER PROCEDURE dbo.usp_Delete_AssemblyPartListByProjectId
  @ProjectId INT,
  @UserId INT = NULL
AS

SET NOCOUNT, XACT_ABORT ON;

WITH cte AS (
    SELECT apl.AssemblyPartListID
    FROM AssemblyPartList apl
    WHERE apl.ProjectID = @ProjectId

    UNION ALL

    SELECT apl.AssemblyPartListID
    FROM cte
    JOIN AssemblyPartList apl ON apl.ParentSequence = cte.AssemblyPartListID
)
DELETE apl
FROM AssemblyPartList apl
JOIN cte ON cte.AssemblyPartList = apl.AssemblyPartList;

做不是使用 READ UNCOMMITTED 。它具有严重的数据完整性影响,除非在特定的调试情况下,否则不应使用。