TFS - Database deploy - Setting CHECK or NOCHECK on Constraints

Question

We are currently using Visual Studio 2010 and have a Database project that contains all of our database objects. We typically deploy the database via a build script to our CI, QA and UAT environments. For Production, we generate the script and provide it to our DBA for deployment.

We have noticed that the deployment for our Production database, is wanting to drop a large number of foreign key constraints and recreate them with NOCHECK.

When we create our Foreign Keys, we do not define CHECK or NOCHECK. Below is an example of one of our keys:

ALTER TABLE [dbo].[Table1]
    ADD CONSTRAINT [FK_Table1_Table2_Field1] 
    FOREIGN KEY ([Field1])
    REFERENCES dbo.[Table2] ([Field1])

When we target our database project at our CI, QA and UAT environments, it wants to create the constraint with "CHECK". It takes the above script and attempts to deploy:

ALTER TABLE [dbo].[Table1] WITH CHECK
    ADD CONSTRAINT [FK_Table1_Table2_Field1] 
    FOREIGN KEY ([Field1])
    REFERENCES dbo.[Table2] ([Field1])

When we target our database project at our Production environment, it wants to create the constraint with "NOCHECK". It takes the above script and attempts to deploy:

ALTER TABLE [dbo].[Table1] WITH NOCHECK
    ADD CONSTRAINT [FK_Table1_Table2_Field1] 
    FOREIGN KEY ([Field1])
    REFERENCES dbo.[Table2] ([Field1])

Is there a setting in my database project that is causing this? Is there a setting on the database that is setting a default as we are not providing one in our script?

When targeting to PROD, does it do an additional check in the script AFTER the above script: ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_Table1_Table2_Field1]? I am assuming your SQL Server version is 2008. — Kash, Aug 19 '11 at 21:35
Or check if the DBA group adds a CHECK ALL constraint in the end like this: ALTER TABLE WITH CHECK CHECK CONSTRAINT ALL. It could be part of their best practices. — Kash, Aug 19 '11 at 21:40

score 0 · Answer 1 · edited May 23 '17 at 12:19

The answer to this question should help you some: WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT

It looks like "WITH CHECK" is the default for new foreign keys and "WITH NOCHECK" is the default for re-enabling foreign keys. This would make sense if you are creating a new database for CI, QA, and UAT every time and only altering your production database (a usual practice).

TFS - Database deploy - Setting CHECK or NOCHECK on Constraints

1 Answers1