SSDT在对比与发布时忽略NOCHECK约束问题求助

I’ve dealt with this exact frustrating behavior in SSDT before—let’s walk through what’s going on and how to fix it:

First, Confirm the Core Issue #

Your observation is spot-on: even when you uncheck the "Ignore check constraints' WITH NOCHECK" and "Ignore foreign keys' WITH NOCHECK" options in your publish profile, SSDT often fails to detect discrepancies between a constraint set with WITH NOCHECK ADD CONSTRAINT followed by CHECK CONSTRAINT (your script) and a plain foreign key definition in the database schema.

Potential Fixes & Workarounds #

1. Explicitly Track the Constraint's Trusted State in SSDT #

SSDT uses the IsNotTrusted property to track whether a foreign key is in a NOCHECK state. To force SSDT to recognize this:

• In your SSDT project, modify the foreign key definition via the table designer:
  1. Open the SurveyQuestion table in the designer
  2. Navigate to the "Foreign Keys" tab
  3. Select FK_SurveyQuestion_Language
  4. Set the "Trusted" property to False, save, then set it back to True
• Alternatively, add this explicit DDL to your project script to ensure the constraint is marked as trusted:

ALTER TABLE [dbo].[SurveyQuestion] 
WITH CHECK CHECK CONSTRAINT [FK_SurveyQuestion_Language];

This command updates the constraint's is_not_trusted flag in sys.foreign_keys, which SSDT uses to compare states.

2. Clear SSDT's Schema Cache #

SSDT sometimes caches old schema data, leading to false "no differences" results. Try these steps:

• Close your SSDT project and restart Visual Studio
• Right-click your project > Select "Refresh Local Schema"
• Delete the .dbmdl file in your project directory (this is the schema cache) then rebuild the project

3. Double-Check Publish Profile Settings #

It’s easy for these settings to get reset accidentally. Reconfirm that both of these options are unchecked in your publish profile:

• Ignore check constraints' WITH NOCHECK
• Ignore foreign keys' WITH NOCHECK
  If you want to verify directly, open the publish profile in an XML editor and ensure these elements are set to False:

<IgnoreCheckConstraintsWithNoCheck>False</IgnoreCheckConstraintsWithNoCheck>
<IgnoreForeignKeysWithNoCheck>False</IgnoreForeignKeysWithNoCheck>

4. Force the State with a Post-Deployment Script #

If SSDT still won’t pick up the difference, use a post-deployment script to enforce the correct constraint state every time you publish:

-- Post-Deployment Script: Ensure FK is checked and trusted
IF EXISTS (
    SELECT 1 
    FROM sys.foreign_keys 
    WHERE name = 'FK_SurveyQuestion_Language' 
    AND is_not_trusted = 1
)
BEGIN
    ALTER TABLE [dbo].[SurveyQuestion] WITH CHECK CHECK CONSTRAINT [FK_SurveyQuestion_Language];
END

Test Case to Validate #

To confirm the fix works, try this test scenario:

1. Create a simple test pair (e.g., ParentTable with an ID column, ChildTable with ParentID) in your SSDT project
2. Define a foreign key in the project using WITH NOCHECK ADD CONSTRAINT followed by CHECK CONSTRAINT
3. Publish to a test database
4. Manually run ALTER TABLE ChildTable NOCHECK CONSTRAINT FK_ChildTable_ParentTable in the test DB
5. Run an SSDT schema comparison—this time it should detect the difference between the trusted state in your project and the untrusted state in the database

I hope one of these solutions resolves the issue for you—it’s a tricky edge case, but SSDT does have ways to handle it once you know where to focus.

内容的提问来源于stack exchange，提问作者user5855178