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.
- Create a temp table that matches the definition of the table into which you want to perform your insert.
- 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.
- 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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| –1: Create Temp table | |
| CREATE TABLE #MyFactTable( | |
| ID INT NULL | |
| , CustomerName VARCHAR(100) NULL | |
| , SourceID INT NULL | |
| , OutputAction VARCHAR(100) NULL | |
| ); | |
| –2: INSERT into the temp table instead of your normal target table | |
| — Merge query will be the same otherwise | |
| INSERT INTO #MyFactTable (ID, CustomerName, SourceID, OutputAction) | |
| SELECT so.ID, so.CustomerName, so.SourceID, so.output_action | |
| FROM ( | |
| MERGE INTO dbo.MyFactTable AS t | |
| USING Staging.MyFactTable AS s | |
| ON ( s.ID = t.ID | |
| AND s.NewLoad = 0 ) | |
| WHEN MATCHED AND ( s.SourceID t.SourceID ) | |
| AND s.NewLoad = 0 | |
| THEN UPDATE | |
| SET RecordState = 0 | |
| , UpdatedDate = getdate() | |
| WHEN NOT MATCHED BY TARGET AND s.NewLoad = 0 THEN | |
| INSERT (ID, CustomerName, SourceID) | |
| VALUES (s.ID, s.CustomerName, s.SourceID) | |
| OUTPUT $action AS OutputAction | |
| , ID | |
| , CustomerName | |
| , SourceID | |
| ) AS so (OutputAction, ID, CustomerName, SourceID) | |
| WHERE OutputAction = 'UPDATE' ; | |
| –3: Perform the final insert into your target table | |
| INSERT INTO MyFactTable (ID, CustomerName, SourceID) | |
| SELECT DISTINCT ID, CustomerName, SourceID | |
| FROM #MyFactTable ; | |
| –4: Clean up your temp objects. | |
| 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.
About Author
paschott
I’ve been working with SQL Server for quite some time. Along the way, I’ve learned quite a few things and realized that I won’t ever know everything about SQL Server. I intended to keep growing and learning to be able to do my job well and share my experiences with others. I currently work for a Health-related non-profit based in Boise as a Database Architect.
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
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)