CODEWITHSUNDEEP
sqlsql-serversql-server-data-toolsdac
user5855178
user5855178

Reputation: 725

SSDT skips NOCHECK constraint in compares and publishes

For some reason when using SSDT to either compare or modify a Table Foreign Key the software does not do anything to change the NOCHECK constraint set on the Foreign Key.

This is the scripted setup for the table Foreign Key:

TABLE:

ALTER TABLE [dbo].[SurveyQuestion]  WITH NOCHECK ADD  CONSTRAINT [FK_SurveyQuestion_Language] FOREIGN KEY([LanguageID])
REFERENCES [dbo].[Language] ([ID])
GO

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

DBSchema:

CONSTRAINT [FK_SurveyQuestion_Language] FOREIGN KEY ([LanguageID]) REFERENCES [dbo].[Language] ([ID])

So, these are different settings, but SSDT just ignores them entirely. Compares come back showing both are identical.

In my publish profile I have "ignore with nocheck on check constraints" unchecked I also have "ignore withnocheck on foreign keys" unchecked. So they both should be evaluated.

Can anyone please try this out in a table of their own? Just change your CHECK to NOCHECK in your database and run SSDT on it to see if SSDT fixes the discrepancy.

I have tried and tried and SSDT keeps bypassing the discrepancy.

Thanks

Upvotes: 3

Views: 995

Answers (1)

Tim
Tim

Reputation: 6060

The only difference between the two settings is whether or not existing data in the table has been checked. In both cases the constraint exists and checking is enabled-- so the schemas are identical-- although the consistency of the data may not be since in the first example it is possible that pre-existing data violates the constraint.

Upvotes: 0

Related Questions