WITH CHECK | NOCHECK OPTION FOR EXISTING DATA CHECK ENFORCEMENT IS IGNORED
compiler-warnings, database-project, sql-server, static-analysis, visual-studio
Solution
It isn't about redundancy, but rather the fact that the project is desired state, or the ideal state of the DDL schema.
When applying the project to an existing database, the process (publish, dacpac deployment, etc) will enforce the check constraints as the delta is applied. The with check and with nocheck are therefore meaningless in the project DDL that defines the table and subsequent constraint(s).
The only place CHECK and NOCHECK are honored are in the pre and post deployment scripts.
EDIT:
If you ever generate a script from a dacpac you will notice things happen in phases.
When it comes to applying new constraints, they are always added WITH NOCHECK. The final phase is applying WITH CHECK to all constraints that need it, AFTER post deployment scripts are run. This allows the maximum amount of customization to occur during the various phases, including the pre and post deployment script phase.
Problem
I am using Visual Studio 2012, with a SQL database project and running database analysis against this database and it is raising the following warning: `WITH CHECK | NOCHECK OPTION FOR EXISTING DATA CHECK ENFORCEMENT IS IGNORED.` My understanding of the error is that the `CHECK` and `NOCHECK` constraints will be ignored for the existing data when the script is run (in each case for me, it is an `ALTER TABLE`). My question is thus, why would a check constraint be ignored? Warning ID number It seems the warning ID has changed so I am including both below so that it is easily searchable in future. In Visual Studio 2010 this had the warning ID of: SQL03159 In Visual Studio 2012 this has the warning ID of: SQL70588 Related Information ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [FK_MyTable_SomeCol]