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

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 775683 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 1/3/2013 3:38:42 AM
Access Restriction Public


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 The Oracle on 6/25/2015 at 3:46 PM
This is a serious flaw, and I do not comprehend how you see this can be "by design." Either you create the FK and it CHECKs, or you don't create the FK. There should be NO middle ground, unless you allow me to set a NOCHECK property for deployment of the FK in the database project (also not wise).
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