WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT

user_40667 0
Viewed 139300

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?

check-constraints tsql sql-server sql
8 Answers

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.

Chris_Hynes_80370

WITH CHECK is indeed the default behaviour however it is good practice to include within your coding.

The alternative behaviour is of course to use WITH NOCHECK, so it is good to explicitly define your intentions. This is often used when you are playing with/modifying/switching inline partitions.

John_Sansom_603080

Dare I say it, it feels like it might be an SSMS (inverted logic) bug; in that the explicit inclusion/use of the 'WITH CHECK' would be needed for the 2nd (existing/reenabled constraint) statement, not the first (new/'with-check' defaulted).

I'm wondering whether they've just applied the generation of the 'WITH CHECK' clause to the wrong SQL statement / the 1st T-SQL statement rather than the 2nd one - assuming they're trying to default the use of the check for both scenarios - for both a new constraint or (the reenabling of) an existing one.

(Seems to make sense to me, as the longer a check constraint is disabled, the theoretically increased chance that broken/check-constraint-invalid data might have crept-in in the meantime.)

user_94072890
Related