Конфликт инструкции delete с ограничением reference sql

I also ran into this issue, and I was able to resolve it. Here is my situation:

In my case, I have a database used for reporting an analytics (MYTARGET_DB), which pulls from a source system (MYSOURCE_DB). Some of the ‘MYTARGET_DB’ tables are unique to that system, and data is created & managed there; Most of the tables are from ‘MYSOURCE_DB’ and there is a job that deletes/ inserts the data into ‘MYTARGET_DB’ from ‘MYSOURCE_DB’.

One of the lookup tables [PRODUCT] is from the SOURCE, and there is a data table [InventoryOutsourced] stored in the TARGET. There is referential integrity designed into the tables. So when I try to run the delete/insert I get this message.

Msg 50000, Level 16, State 1, Procedure uspJobInsertAllTables_AM, Line 249
The DELETE statement conflicted with the REFERENCE constraint "FK_InventoryOutsourced_Product". The conflict occurred in database "ProductionPlanning", table "dbo.InventoryOutsourced", column 'ProdCode'.

The workaround I created is to insert data into [@tempTable] table variable from [InventoryOutsourced], delete data in [InventoryOutsourced], run the synch jobs, insert into [InventoryOutsourced] from [@tempTable]. This keeps the integrity in place, and the unique data collection is also retained. Which is the best of both worlds. Hope this helps.

BEGIN TRY
    BEGIN TRANSACTION InsertAllTables_AM

        DECLARE
        @BatchRunTime datetime = getdate(),
        @InsertBatchId bigint
            select @InsertBatchId = max(IsNull(batchid,0)) + 1 from JobRunStatistic 

        --<DataCaptureTmp/> Capture the data tables unique to this database, before deleting source system reference tables
            --[InventoryOutsourced]
            DECLARE @tmpInventoryOutsourced as table (
                [ProdCode]      VARCHAR (12)    NOT NULL,
                [WhseCode]      VARCHAR (4)     NOT NULL,
                [Cases]          NUMERIC (8)     NOT NULL,
                [Weight]         NUMERIC (10, 2) NOT NULL,
                [Date] DATE NOT NULL, 
                [SourcedFrom] NVARCHAR(50) NOT NULL, 
                [User] NCHAR(50) NOT NULL, 
                [ModifiedDatetime] DATETIME NOT NULL
                )

            INSERT INTO @tmpInventoryOutsourced (
                [ProdCode]
               ,[WhseCode]
               ,[Cases]
               ,[Weight]
               ,[Date]
               ,[SourcedFrom]
               ,[User]
               ,[ModifiedDatetime]
               )
            SELECT 
                [ProdCode]
                ,[WhseCode]
                ,[Cases]
                ,[Weight]
                ,[Date]
                ,[SourcedFrom]
                ,[User]
                ,[ModifiedDatetime]
            FROM [dbo].[InventoryOutsourced]

            DELETE FROM [InventoryOutsourced]
        --</DataCaptureTmp> 

... Delete Processes
... Delete Processes    

        --<DataCaptureInsert/> Capture the data tables unique to this database, before deleting source system reference tables
            --[InventoryOutsourced]
            INSERT INTO [dbo].[InventoryOutsourced] (
                [ProdCode]
               ,[WhseCode]
               ,[Cases]
               ,[Weight]
               ,[Date]
               ,[SourcedFrom]
               ,[User]
               ,[ModifiedDatetime]
               )
            SELECT 
                [ProdCode]
                ,[WhseCode]
                ,[Cases]
                ,[Weight]
                ,[Date]
                ,[SourcedFrom]
                ,[User]
                ,[ModifiedDatetime]
            FROM @tmpInventoryOutsourced
            --</DataCaptureInsert> 

    COMMIT TRANSACTION InsertAllTables_AM
END TRY

You are trying to Delete the record from a Table which has a reference in another Table.

Here REFERENCE constraint is «FK_User_History_Tbl_Customer«.

Tables are User_History and Tbl_Customer.
UserId column is referenced by Tbl_Customer Table.

When you try to delete a row from User_History Table, it comes to know that the same row has some related row in Tbl_Customer Table.

So, you need to delete from Tbl_Customer Table first and then delete from Table User_History.

If you want to delete a row, then you have to execute the following two queries in sequence.

DELETE FROM Tbl_Customer
WHERE UserId = [[[UserIdToDelete]]];

DELETE FROM User_History
WHERE UserId = [[[UserIdToDelete]]]