SQL Server Home
Unable to perform Slowly Changing Dimension updates on a dimension table with a foreign key
4/24/2009 8:12:36 AM
User(s) can reproduce this bug
A typical scenario in data warehouse/business intelligence development is the maintenance of Slowly Changing Dimensions. This involves the insertion of new rows into a dimension, Type 1 SCD updates to a dimension and Type 2 SCD updates to a dimension. In many cases all three types of change can be present in a single feed.
The new MERGE syntax in SQL Server 2008 in combination with the INSERT...FROM...OUTPUT functionality can be leveraged to perform all of these changes in a single Transact SQL statement.
I have written and used this code successfully. However, as soon as a foreign key relationship is created on the dimension table being updated the code fails with the following error
"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'."
Whilst this behaviour appears to be deliberate I think it should be classed as a bug on the basis that requiring that referential integrity be removed from the table in question before the SCD changes are applied seems ludicrous.
I would expect the changes to be applied in the same way, with or without referential integrity checks in place.
SQL Server 2008 - Developer Edition
Windows Vista 64-bit SP1
Operating System Language
Steps to Reproduce
Run the attached SQL script to reproduce on any SQL Server 2008 database
The attempt to perform SCD updates fails when a foreign key exists against the table
The SCD updates should be applied regardless of whether or not a foreign key relationship exists
to post a comment.
Please enter a comment.
on 6/19/2013 at 6:12 AM
This still exists in SQL Server 2012.
on 4/30/2009 at 2:59 PM
Thanks for your feedback Colin. I'll have someone else review this further. -Eric
to post a workaround.
Please enter a workaround.
Peter A. Schott
on 1/17/2011 at 10:25 AM
You can also generate a Temp table to match your target table, and insert into that table using MERGE, then insert your desired table after the Merge by selecting from that temp table. I've posted some of the details on my blog.
(thanks to Chris Harshman at Stack Overflow for the idea)
on 4/24/2009 at 8:14 AM
To perform the update remove the foreign key constraint from the table, perform the update and then reapply the constraint
INSERT...MERGE bug repro script.sql (restricted)
© 2013 Microsoft