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:
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.