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.

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.

Advertisements

2 thoughts on “SQL 2008 Merge and PK/FK Constraints

  1. Thanks for the solution.

    My only suggestion would be to replace the CREATE TABLE for the temporary table with

    SELECT * into #MyFactTable
    FROM MyFactTable
    WHERE
    1=0

    so that you don't have to recreate the full table definition in the proc.

    Cheers,
    Dan

    Like

  2. Dan,
    I've tried to be a bit better about explicitly defining my tables when creating various stored procedures. That practice has saved me from surprises later on when something small changes in a table definition or something similar. I've definitely used that as a shortcut in the past, but try to avoid it inside of a stored proc or similar object.

    It's a great shortcut for creating a table structure, though. That WHERE 1=2 or 1=0 shortcut helps me quite a bit when I am in the middle of a lot of ad-hoc work.

    I hope that it helps you a bit. I know it surprised me quite a bit that this didn't just work and I figured there had to be a decent workaround. (or at least _a_ workaround)

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s