先检查加约束，然后再检查加对比 [英] WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT

问题描述

我正在查看SQL Server 2008的AdventureWorks示例数据库，并且在他们的创建脚本中看到他们倾向于使用以下内容:

ALTER TABLE [Production].[ProductCostHistory] WITH CHECK ADD 
CONSTRAINT [FK_ProductCostHistory_Product_ProductID] FOREIGN KEY([ProductID])
  REFERENCES [Production].[Product] ([ProductID])
GO

紧随其后的是:

ALTER TABLE [Production].[ProductCostHistory] CHECK CONSTRAINT     
[FK_ProductCostHistory_Product_ProductID]
GO

对于外键(如此处)，唯一约束和常规CHECK约束，我看到了这一点； DEFAULT约束使用我更熟悉的常规格式，例如:

ALTER TABLE [Production].[ProductCostHistory] ADD  CONSTRAINT  
[DF_ProductCostHistory_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

第一种方法与第二种方法有什么区别(如果有)?

解决方案

第一种语法是多余的-WITH CHECK默认用于新约束，并且默认情况下也打开约束.

此语法是由SQL Management Studio在生成sql脚本时生成的-我假设它具有某种额外的冗余性，可能即使在更改表的默认约束行为时也可以确保启用了约束. >

I'm looking at the AdventureWorks sample database for SQL Server 2008, and I see in their creation scripts that they tend to use the following:

ALTER TABLE [Production].[ProductCostHistory] WITH CHECK ADD 
CONSTRAINT [FK_ProductCostHistory_Product_ProductID] FOREIGN KEY([ProductID])
  REFERENCES [Production].[Product] ([ProductID])
GO

followed immediately by :

ALTER TABLE [Production].[ProductCostHistory] CHECK CONSTRAINT     
[FK_ProductCostHistory_Product_ProductID]
GO

I see this for foreign keys (as here), unique constraints and regular CHECK constraints; DEFAULT constraints use the regular format I am more familiar with such as:

ALTER TABLE [Production].[ProductCostHistory] ADD  CONSTRAINT  
[DF_ProductCostHistory_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

What is the difference, if any, between doing it the first way versus the second?

解决方案

The first syntax is redundant - the WITH CHECK is default for new constraints, and the constraint is turned on by default as well.

This syntax is generated by the SQL management studio when generating sql scripts -- I'm assuming it's some sort of extra redundancy, possibly to ensure the constraint is enabled even if the default constraint behavior for a table is changed.

这篇关于先检查加约束，然后再检查加对比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！