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.