SQL 2008 Merge and PK/FK Constraints

We ran into this issue a while back.  SQL 2008 has a “feature” that will not allow inserts into a table on either side of a Foreign Key relationship.  If you attempt to insert into a table involved in the FK relationship, you get an error something like:

The target table 'TableName' 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. Found reference constraint 'ConstraintName'.

This is documented in Connect 435031. There is one workaround documented in the ticket – drop the FK constraint, run the MERGE query, then re-create the constraint. Happily, this bug is still open as the requirement to drop a constraint just to run a MERGE statement kind of defeats the purpose of putting a constraint on the table in the first place. While dropping the constraints will work, there is another possible workaround. I was able to run this without any issues while leaving my FK Constraint in place.

1. Create a temp table that matches the definition of the table into which you want to perform your insert.
2. Instead of inserting into your base table like you normally would in a merge, write your code to INSERT #Tablename at the beginning of the MERGE process. You’ll still use your main table and staging table inside the MERGE INTO … USING query.
3. After the Merge, insert into the main table using the values in your Temp table.

Here’s a shell of an example. Line 11 below contains the main difference to the MERGE query from a “normal” MERGE.

   1: --1: Create Temp table

   2: CREATE TABLE #MyFactTable(

   3:     ID INT NULL

   4:     , CustomerName VARCHAR(100) NULL

   5:     , SourceID INT NULL

   6:     , OutputAction VARCHAR(100) NULL

   7: );

   8:  

   9: --2: INSERT into the temp table instead of your normal target table

  10: --   Merge query will be the same otherwise

  11: INSERT INTO #MyFactTable (ID, CustomerName, SourceID, OutputAction)

  12: SELECT so.ID, so.CustomerName, so.SourceID, so.output_action

  13: FROM (

  14:     MERGE INTO dbo.MyFactTable AS t

  15:     USING Staging.MyFactTable AS s

  16:     ON ( s.ID = t.ID

  17:         AND s.NewLoad = 0 )

  18:         

  19:     WHEN MATCHED AND ( s.SourceID <> t.SourceID )

  20:             AND s.NewLoad = 0            

  21:     THEN UPDATE 

  22:         SET RecordState = 0

  23:         , UpdatedDate = getdate()

  24:         

  25:     WHEN NOT MATCHED BY TARGET AND s.NewLoad = 0 THEN

  26:         INSERT (ID, CustomerName, SourceID)

  27:         VALUES (s.ID, s.CustomerName, s.SourceID)

  28:         OUTPUT $action AS OutputAction

  29:             , ID

  30:             , CustomerName

  31:             , SourceID

  32:         ) AS so (OutputAction, ID, CustomerName, SourceID)

  33:         WHERE OutputAction = 'UPDATE'  ;

  34:  

  35: --3: Perform the final insert into your target table

  36: INSERT INTO MyFactTable (ID, CustomerName, SourceID)

  37: SELECT DISTINCT ID, CustomerName, SourceID

  38: FROM #MyFactTable ;

  39:  

  40: --4: Clean up your temp objects.

  41: DROP TABLE #MyFactTable ;

I’d love to hear if anyone else has a workaround for this that doesn’t involve dropping and recreating the FK constraint. If this is an issue for you, please vote for the Connect Ticket.