Monday, January 17, 2011

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.