Home Dashboard Directory Help

[SSDT bug] Publish will fail due to FK constraint and then succeed immediately after by Jamie Thomson


 as By Design Help for as By Design

Sign in
to vote
Type: Bug
ID: 775683
Opened: 1/3/2013 3:38:42 AM
Access Restriction: Public
User(s) can reproduce this bug


I have attempted to Publish an SSDT project to an existing database that already had data in it. My Publish operation attempted to create a FOREIGN KEY constraint and there was data in the database that violated the FOREIGN KEY constraint and hence the Publish operation failed with error:
(10036,1): SQL72014: .Net SqlClient Data Provider: Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "ActualsDay_fct_ent_CustomerHierarchyLvl03_FK_PayTo". The conflict occurred in database "PM_EDW", table "ent.CustomerHierarchyLvl03", column 'CstHierLvl03_EK'.

This is all fine. The failure is expected. However, be aware that the FOREIGN KEY *has* been created, its just that the checking of existing data is not enabled. In other words, the following query returns a row:
FROM sys.foreign_keys
WHERE name = 'ActualsDay_fct_ent_CustomerHierarchyLvl03_FK_PayTo'
AND is_not_trusted=1 --WITH CHECK

The problem is that if I re-attempt the same Publish operation *without changing anything* that Publish operation will succeed. The reason is that SSDT only checks to see if the FOREIGN KEY exists (which of course, it does, it was created on the previous Publish operation). It does not check the state of the FOREIGN KEY. i.e. It does not check whether the FOREIGN KEY has WICH CHECK enabled.

To my mind this is a bug. SSDT has left an object in an undesired state and, worse than that, the user may have no idea that his/her database contains invalid data.

I have blogged about this at: http://sqlblog.com/blogs/jamie_thomson/archive/2013/01/03/warning-publish-may-fail-on-creation-of-a-foreign-key-constraint-and-then-immediately-succeed-ssdt.aspx and the blog post contains screenshots which may help to explain the problem better.

Sign in to post a comment.
Posted by Microsoft on 1/25/2013 at 2:08 PM
Hey Jamie,

Thanks a ton for your thorough feedback as well as the great blog write-up documenting this issue. This is expected, as we do not rollback object/constraint deployment if the checking of an associated constraint fails after the schema has been deployed. In addition, we currently do not model the "trusted-ness" of a constraint as a comparable property, which leads to the lack of change on the subsequent deployment that you point out. We are not currently looking to address this with a fix in the deployment pipeline.

Please let us know if this continues to cause folks issues.

Thanks again,
Adam Mahood
Program Manager
SQL Server Database Systems
Sign in to post a workaround.