TFS - 数据库部署 - 设置 CHECK 或 NOCHECK 约束
我们当前使用的是 Visual Studio 2010，并且有一个包含所有数据库对象的数据库项目。我们通常通过构建脚本将数据库部署到 CI、QA 和 UAT 环境中。对于生产，我们生成脚本并将其提供给 DBA 进行部署。
我们注意到生产数据库的部署希望删除大量外键约束并使用 NOCHECK 重新创建它们。
当我们创建外键时，我们没有定义CHECK或NOCHECK。下面是我们的关键之一的示例：
ALTER TABLE [dbo].[Table1]
ADD CONSTRAINT [FK_Table1_Table2_Field1]
FOREIGN KEY ([Field1])
REFERENCES dbo.[Table2] ([Field1])
当我们在 CI、QA 和 UAT 环境中定位数据库项目时，它希望使用“CHECK”创建约束。它采用上述脚本并尝试部署：
ALTER TABLE [dbo].[Table1] WITH CHECK
ADD CONSTRAINT [FK_Table1_Table2_Field1]
FOREIGN KEY ([Field1])
REFERENCES dbo.[Table2] ([Field1])
当我们在生产环境中定位数据库项目时，它希望使用“NOCHECK”创建约束。它采用上述脚本并尝试部署：
ALTER TABLE [dbo].[Table1] WITH NOCHECK
ADD CONSTRAINT [FK_Table1_Table2_Field1]
FOREIGN KEY ([Field1])
REFERENCES dbo.[Table2] ([Field1])
我的数据库项目中是否有导致此问题的设置？由于我们没有在脚本中提供默认值，数据库上是否有一项设置被设置为默认值？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这个问题的答案应该对你有一些帮助：
先检查添加约束，然后检查约束与 ADD CONSTRAINT
看起来“WITH CHECK”是新外键的默认值，“WITH NOCHECK”是新外键的默认值重新启用外键。如果您每次都为 CI、QA 和 UAT 创建新数据库并且仅更改生产数据库（通常的做法），那么这是有意义的。
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).