EN VI

C# - DELETE statement conflicted with the SAME TABLE REFERENCE constraint. SQL issue?

How to C# - DELETE statement conflicted with the SAME TABLE REFERENCE constraint. SQL issue

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

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'.

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

I can't delete the rows using the stored procedure to delete project data from table. Unsure why as it is null but still has a reference error. Stored proc used in application to clear data from generated table.

Solution:

The problem is that you have a self-referencing foreign key, which is preventing you deleting this row because there are child rows dependent on it. The UPDATE statement isn't helping as that will only affect the parent rows, it's the dependent rows you need to worry about.

You can use a recursive CTE to find all rows (and recursively all child rows) to delete. As long as you delete them all at once then the server will ensure they all get deleted in the right order.

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;

Do NOT use READ UNCOMMITTED. It has serious data integrity implications, and should not be used except in specific debugging circumstances.