Composable DML (Merge… Insert) with Foreign Key Issue and Solution

You can use Merge with INSERT (Composable DML) to log historic data. But you cannot insert into a table with a FOREIGN KEY defined. You will run into error like this:
” The target table ‘myTable’ of the INSERT statement cannot be on either side of a (primary key, foreign key)
relationship when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.”

A workaround is to disable (or drop) these constraints before Insert and enable these constrains afterwards.

--mylog table has two foreign keys:[FK_myLog_a], [FK_myLog_s] 
--

--disable constraints
ALTER TABLE [dbo].[myLog] NOCHECK CONSTRAINT ALL



INSERT INTO [dbo].[myLog]
SELECT Action, ISNULL(Newaccountid ,Oldaccountid) as accountid, 
ISNULL(Newserviceid,Oldserviceid) as serviceid, changedDate

FROM
 
( 

MERGE asTable AS target
USING (
SELECT b.accountid,t1.Serviceid
FROM   mytable) AS source (Newid, Newsid)
ON (target.id = source.Newid )

WHEN NOT MATCHED BY TARGET  
--Insert  new
    THEN INSERT (accountid, Serviceid) VALUES (Newaccountid, NewServiceid)
        
WHEN NOT MATCHED BY SOURCE AND (target.id <>4)
--Delete old  
THEN  DELETE

OUTPUT $action, Inserted.id, Inserted.sid, Deleted.id,Deleted.sid, GETDATE()

)
 AS Changes (Action, Newid, Newsid,Oldid, Oldsid, changedDate) 
 WHERE Action = 'DELETE' Or Action = 'INSERT';






--Enable constraints after the operation
ALTER TABLE [dbo].[accountserviceLog] CHECK CONSTRAINT ALL

ALTER TABLE [dbo].[myLog] WITH CHECK CHECK CONSTRAINT [FK_myLog_a] 
ALTER TABLE [dbo].[myLog] WITH CHECK CHECK CONSTRAINT [FK_myLog_s] 


--http://msdn.microsoft.com/en-us/library/bb510625(v=sql.105).aspx
--http://www.brentozar.com/blitz/foreign-key-trusted/

---