how to handle foreign key constraints on a merge statement?

Question

I am currently doing a merge on the following table between my test_db (target) and the same table on a different database called tablebackups (source).

 IF OBJECT_ID('[dbo].[tblBCatalogueType]') IS NOT NULL 
 DROP TABLE [dbo].[tblBCatalogueType] 
 GO
 CREATE TABLE [dbo].[tblBCatalogueType] ( 
 [sintCatalogueTypeID] SMALLINT IDENTITY(1,1) NOT NULL,
 [blnIsCurrent] BIT NOT NULL,
 [tsRowVersion] TIMESTAMP NOT NULL,
 CONSTRAINT [PK_tblProdCatalogueType] 
PRIMARY KEY CLUSTERED ([sintCatalogueTypeID] asc) 
WITH FILLFACTOR = 97)

When I have a look at the tables that would be referencing my tlbBCatalogueType table I get the following list:

script to find foreign key constraints:

----------------------------------------------------------------------------
declare @referenced_table sysname 
declare @ref_Obj int 
declare @table sysname 
declare @Obj int 
select @table = 'dbo.tblBCatalogueType' --'tblBCataloguePriceSetItem' 
select @referenced_table = 'dbo.tblBCatalogueType' --'dbo.tblBCataloguePriceSet' 
select @obj = OBJECT_ID(@table)
select @ref_obj = OBJECT_ID(@referenced_table)
SELECT obj.name AS FK_NAME,
 sch.name AS [schema_name],
 tab1.name AS [table],
 col1.name AS [column],
 sch.name AS [referenced_schema],
 tab2.name AS [referenced_table],
 col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
 ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
 ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
 ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
 ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas sch2
 ON tab2.schema_id = sch2.schema_id
INNER JOIN sys.columns col2
 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
where 1=1
 AND ( ( @OBJ IS NULL OR @obj = tab1.object_id) 
 OR ( @ref_obj IS NULL OR @ref_obj = tab2.object_id )) 

enter image description here

this is my merge statement:

 SET NOCOUNT ON;
 DECLARE @TranCountAtStart INT;
 SET @TranCountAtStart = @@TRANCOUNT;
 -- SELECT @@TRANCOUNT,XACT_STATE();
 IF @TranCountAtStart = 0
 BEGIN TRANSACTION
 ELSE
 SAVE TRANSACTION USP_Procedure_Name;
 BEGIN TRY
------------------------------------------------------------------------------------------- 
-- the dbo.tblBCataloguePriceSet must be run before this merge.
-- Message: The MERGE statement conflicted with the REFERENCE constraint "fk_CataloguePriceSet_CatalogueType". 
-- The conflict occurred in database "Bocss2", table "dbo.tblBCataloguePriceSet", column 'sintCatalogueTypeID'. 
 IF OBJECT_ID( 'tablebackups.dbo.tblBCatalogueType_log') IS NOT NULL DROP TABLE tablebackups.dbo.tblBCatalogueType_log;
 CREATE TABLE tablebackups.dbo.tblBCatalogueType_log(
 ChangeType NVARCHAR(10)
 ,sintCatalogueTypeID SMALLINT NOT NULL
 ,DateTimeChanged DateTime NOT NULL);
 BEGIN TRANSACTION T1
 SET IDENTITY_INSERT dbo.tblBCatalogueType ON;
 SELECT @@TRANCOUNT
 SELECT XACT_STATE()
 MERGE dbo.tblBCatalogueType AS TARGET
 USING tablebackups.dbo.tblBCatalogueType AS SOURCE
 ON TARGET.sintCatalogueTypeID = SOURCE.sintCatalogueTypeID
 WHEN MATCHED
 THEN UPDATE SET 
 [blnIsCurrent] = SOURCE.blnIsCurrent
 WHEN NOT MATCHED BY TARGET
 THEN INSERT( [sintCatalogueTypeID]
 ,[blnIsCurrent]
 )
 VALUES( 
 SOURCE.[sintCatalogueTypeID]
 ,SOURCE.[blnIsCurrent]
 )
 WHEN NOT MATCHED BY SOURCE
 THEN DELETE 
 --$action specifies a column of type nvarchar(10) 
 --in the OUTPUT clause that returns one of three 
 --values for each row: 'INSERT', 'UPDATE', or 'DELETE', 
 --according to the action that was performed on that row
 -------------------------------------
 OUTPUT
 $ACTION ChangeType,
 coalesce (inserted.sintCatalogueTypeID, deleted.sintCatalogueTypeID) sintCatalogueTypeID,
 Getdate () DateTimeChanged
 INTO tablebackups.dbo.tblBCatalogueType_log
 -------------------------------------
 ;
 SELECT @@ROWCOUNT;
 SET IDENTITY_INSERT dbo.tblBCatalogueType OFF;
 COMMIT TRANSACTION T1 
-------------------------------------------------------------------------------------------
 END TRY
 BEGIN CATCH
 SET IDENTITY_INSERT dbo.tblBCatalogueType OFF;
 DECLARE @ERRORMESSAGE NVARCHAR(512),
 @ERRORSEVERITY INT,
 @ERRORNUMBER INT,
 @ERRORSTATE INT,
 @ERRORPROCEDURE SYSNAME,
 @ERRORLINE INT,
 @XASTATE INT
 SELECT
 @ERRORMESSAGE = ERROR_MESSAGE(),
 @ERRORSEVERITY = ERROR_SEVERITY(),
 @ERRORNUMBER = ERROR_NUMBER(),
 @ERRORSTATE = ERROR_STATE(),
 @ERRORPROCEDURE = ERROR_PROCEDURE(),
 @ERRORLINE = ERROR_LINE()
 SET @ERRORMESSAGE = 
 (
 SELECT CHAR(13) +
 'Message:' + SPACE(1) + @ErrorMessage + SPACE(2) + CHAR(13) +
 'Error:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorNumber) + SPACE(1) + CHAR(13) +
 'Severity:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorSeverity) + SPACE(1) + CHAR(13) +
 'State:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorState) + SPACE(1) + CHAR(13) +
 'Routine_Name:' + SPACE(1) + coalesce(@ErrorProcedure,'') + SPACE(1) + CHAR(13) +
 'Line:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorLine) + SPACE(1) + CHAR(13) +
 'Executed As:' + SPACE(1) + SYSTEM_USER + SPACE(1) + CHAR(13) +
 'Database:' + SPACE(1) + DB_NAME() + SPACE(1) + CHAR(13) +
 'OSTime:' + SPACE(1) + CONVERT(NVARCHAR(25),CURRENT_TIMESTAMP,121) + CHAR(13) 
 )
 SELECT @XASTATE = XACT_STATE();
 IF @XASTATE = - 1
 ROLLBACK;
 IF @XASTATE = 1
 AND @TranCountAtStart = 0
 ROLLBACK
 IF @XASTATE = 1
 AND @TranCountAtStart > 0
 ROLLBACK TRANSACTION USP_Procedure_Name;
 --We can also save the error details to a table for later reference here.
 RAISERROR (@ERRORMESSAGE,16,1)
 END CATCH

How could I add a error-handling process to my merge?

Most probably it breaks on the delete.

 WHEN NOT MATCHED BY SOURCE
 THEN DELETE 

Here is where I should have something to handle it.

Let's keep it simple, and just say - if it breaks by a constraint violation - just don't delete the record.

How can I get this done?

Answer 1

Well, "just don't delete the record" aside, you can change the foreign keys to be on delete cascade or on delete set null. This will delete everything when you delete the parent, or set the child keys to null, respectively.

Those aren't always great behaviours. For example if a customer deleted their account you probably don't want to drop all their order history and tax invoices as well.

An ugly, hideous, but common alternative, is:

• If you know the schema isn't going to change (or you'll be the one doing it), you can just include a sub-query which will exclude those keys that you don't want to delete. This isn't too bad.

• If you wanted to get really ugly, you can use your procedure there to create dynamic SQL to do the same (putting it into a temp table and then excluding that from your results). Don't do this unless you have to.

• You could use some combination of these with an [instead of delete] trigger. This is probably the worst of all.