0

Given the following Foreign Key Constraint definition inline of a Create Table definition in a SSDT project:

CREATE TABLE A
(...Columns...),
CONSTRAINT [FK_O] FOREIGN KEY ([OID]) REFERENCES [dbo].[O] ([ID]) ON DELETE CASCADE NOT FOR REPLICATION,
(...)

and the following quote from MSDN

WITH CHECK | WITH NOCHECK Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

, I wonder why the FIRST deployment result of this NEW table does look like that using a constraint WITH NOCHECK option as follows:

ALTER TABLE [dbo].[A]  WITH NOCHECK ADD CONSTRAINT [FK_O] FOREIGN KEY([OID])
REFERENCES [dbo].[O] ([ID])
ON DELETE CASCADE
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[A] CHECK CONSTRAINT [FK_O]
GO

So the question is, shouldn't the inline Constraint definition that does not allow to define "with Nocheck" paired with the rule from the MSDB page result into a constraint using the WITH CHECK option on this NEW table? Why do we get a WITH NOCHECK Constraint here?

0

NOT FOR REPLICATION means that the constraint is never checked, even if you check it explicitly.

Probably, SSDT here is making explicit something that is implicit.

  • This is the correct answer. Unfortunately, because it's accidental that NOT FOR REPLICATION is part of this sample Constraint. The actual question was supposed to focus on why ssdt does or does not change the constraint definition in the target tables kind of ramdomly. – Magier Sep 12 '16 at 12:30
0

There is only one kind of foreign key constraint (though it can be trusted, or untrusted).

If it was a new table, both code paths are essentially the same. But they do it this way because SSDT is taking into account it might need to tweak the DDL to add to an existing table.

  • If the table existed and it altered the table with check, the deployment will fail and will not put a check constraint on the table.

  • If the table existed and it altered the table with nocheck, leaving the check for afterwards, you'll get an error if old data doesn't meet the criteria, but at least the schema change will be in place.

What they've done is generate a consistent set of code that covers both non-existing and existing possibilities. They haven't tried to optimise for every use case.

Essentially it has no further inherent meaning. It's just an artefact of how they output the code.

  • It IS a new table (I added this information in the question) - and this is the weird thing, why should WITH NOCHECK be added to a new, empty table, this does not make sense to me. – Magier Sep 12 '16 at 10:14
  • I think you understand that SSDT doesn't just create tables - it can compare to an existing database (or its own internal snapshot of a previous version of a project) and output DDL to modify the existing tables. So what they've done is just have one consistent way of generating the statements - it doesn't try to create extremely optimised code for each possible variation. – Cody Konior Sep 12 '16 at 11:18
  • In my scenario we had issues during publishing to production because suddenly our master db was having CHECK constraints and the publishing process was blocked by the attempt to change the constraints in production from WITH NOCHECK to WICH CHECK. This failed. So I am trying to find out why the master schema was changed from WITH NOCHECK to WITH CHECK by SSDT. To me it looks randomly what SSDT does.I created a combinated CREATE TABLE/ALTER CONSTRAINT script and changed WITH (NO)CHECK for and back, but it's ignored. <IgnoreWithNocheckOnForeignKeys> in PublishingProfile doesn't change this... – Magier Sep 12 '16 at 12:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.