Warning! Publish may fail on creation of a FOREIGN KEY constraint and then immediately succeed [SSDT]

I have stumbled across a nuance of SSDT that other users should be cognizant of. I published an SSDT database project onto an existing database that already had data in it but that Publish operation failed:

The error message was:

(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'.

The command that caused the error was:

ALTER TABLE [fct].[ActualsDay] WITH CHECK CHECK CONSTRAINT [ActualsDay_fct_ent_CustomerHierarchyLvl03_FK_PayTo];

In other words SSDT created a FOREIGN KEY constraint in my database and then attempted to check that existing data satisfied that constraint (hence the WITH CHECK of the command above). There was some data that violated that constraint and the Publish operation failed.
This is good, I definitely want a Publish operation to fail under these circumstances.

If you want to understand more about WITH CHECK take a read of How to load related tables.

A problem occurred though when I next published because that Publish operation actually succeeded:

even though I hadn’t changed anything in the project! If I check out the metadata of that FOREIGN KEY:

SELECT	name,is_not_trusted
FROM	sys.foreign_keys  WHERE	name = 'ActualsDay_fct_ent_CustomerHierarchyLvl03_FK_PayTo' 

I see that [is_not_trusted] equals 1.

What does that mean, exactly? Well, SSMS’s table designer provides more useful information:

[is_not_trusted] means that my new FOREIGN KEY constraint has been created but the existing data has not been checked for compliance. Ouch! Not good, not good at all!

What’s going on here? If we break down the individual steps it becomes evident:

1. First Publish operation starts
2. FOREIGN KEY constraint gets created but without the WITH CHECK option
3. FOREIGN KEY constraint is ALTERed to have the WITH CHECK option. This fails due to existing data violating the constraint and hence the entire Publish operation fails
4. Second Publish operation starts
5. Publish engine only checks to see that the FOREIGN KEY constraint exists, which it does. Hence, no action is taken
6. Second Publish operation succeeds and I’m left with data that violates a newly created FOREIGN KEY constraint

This sequence of events can leave your database in a dangerous state and hence is something that SSDT users should be aware of. In my opinion this is a bug and I have raised it as such on Microsoft Connect: [SSDT bug] Publish will fail due to FK constraint and then succeed immediately after.

Be aware, be very aware!

@Jamiet