3

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

user5855178
  • 567
  • 1
  • 7
  • 17

1 Answers1

0

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.

Tim
  • 5,940
  • 1
  • 12
  • 18
  • No, actually it says WITH NOCHECK in the database table markup. Try it yourself in a db table that you have ssdt setup for. Set the db table column key to WITH NOCHECK and see if SSDT rolls that back on publish. I have tried this many many times in many databases. You change the setting in the db table column and run SSDT publish, but SSDT just ignores the difference entirely. Try it. – user5855178 May 12 '18 at 23:37
  • The second part of your statement, `ALTER TABLE [dbo].[SurveyQuestion] CHECK CONSTRAINT [FK_SurveyQuestion_Language]` turns the CHECK back on, regardless of whether you create the constraint `WITH CHECK` or `WITH NOCHECK` – Tim May 12 '18 at 23:43
  • And yes, I get what you are saying, that this is an indicator of an event, data more than schema. My point is SSDT should sycnhronize the project schema to the database table schema if I change the setting, but it does not. SSDT say the two tables are identical, but they aren't. That's a defect. – user5855178 May 12 '18 at 23:52
  • I also ran the same compare in RedGate Schema Compare and RedGate reports the discrepancy as expected. This is the only difference in the database table is NOCHECK. Microsoft says they are identical, but Redgate says not identical. This appears to be a Microsoft defect, or there is a setting someplace that I have missed. – user5855178 May 13 '18 at 00:00
  • It's especially weird since foreign keys are created by the schema comparer with NOCHECK and then it's set to CHECK in a second step. If that fails, the database is in a state where SSDT doesn't understand that it hasn't completed it's own job. – John Sep 26 '22 at 20:54