Comparative Analysis of WITH CHECK ADD CONSTRAINT and CHECK CONSTRAINT in SQL Server

In SQL Server database development, constraint creation and management are crucial for ensuring data integrity. The creation scripts of the AdventureWorks sample database demonstrate a common pattern: using the WITH CHECK ADD CONSTRAINT statement immediately followed by a CHECK CONSTRAINT command. For example, for foreign key constraint creation:

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

In contrast, default constraints typically use a more concise format:

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

Default Behavior and Redundancy Analysis

According to the best answer analysis, the first syntax is actually redundant. In SQL Server, WITH CHECK is the default option for new constraints, meaning that existing data is automatically checked for compliance when a constraint is created. Similarly, constraints are enabled by default after creation. Therefore, the separate CHECK CONSTRAINT statement is usually unnecessary. This pattern is common in scripts generated by SQL Server Management Studio, possibly to provide additional redundancy, ensuring constraints remain enabled even if the table's default behavior changes.

Constraint Trust Mechanism Details

The trust status of constraints significantly impacts data integrity and query optimization. Constraints created with WITH CHECK are marked as "trusted," meaning the database engine trusts that the constraint accurately reflects the current data state in the table. Conversely, constraints created with WITH NOCHECK are marked as "not trusted," for example:

ALTER TABLE T2 WITH NOCHECK ADD CONSTRAINT FK_T2_T1 FOREIGN KEY (FKID) REFERENCES T1 (ID);

Untrusted constraints only validate future inserted or modified data, ignoring violations in existing records. This can be queried via system views:

SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1;
SELECT * FROM sys.check_constraints WHERE is_not_trusted = 1;

Data Validation and Error Handling

When data in the table violates constraint conditions, WITH CHECK ADD CONSTRAINT fails, preventing the creation of invalid constraints. For instance, if there are orphaned records in a child table, adding a foreign key constraint will fail:

-- Assuming T2 has records with FKID=3, but T1 has no ID=3 records
ALTER TABLE T2 WITH CHECK ADD CONSTRAINT FK_T2_T1 FOREIGN KEY (FKID) REFERENCES T1 (ID);  -- Fails

To resolve this, one can create the constraint with WITH NOCHECK, clean the data, and then re-enable the constraint:

ALTER TABLE T2 WITH NOCHECK ADD CONSTRAINT FK_T2_T1 FOREIGN KEY (FKID) REFERENCES T1 (ID);  -- Succeeds
DELETE FROM T2 WHERE FKID NOT IN (SELECT ID FROM T1);  -- Clean orphaned records
ALTER TABLE T2 WITH CHECK CHECK CONSTRAINT FK_T2_T1;  -- Succeeds in enabling and validating

Impact on Query Optimizer

Untrusted constraints are ignored by the query optimizer, which may affect query performance. For example, in join operations, the optimizer cannot leverage untrusted foreign key constraints for optimization, potentially leading to suboptimal execution plans. Thus, maintaining trusted constraints is essential for database performance.

Syntax Parsing and Best Practices

In the statement ALTER TABLE ... WITH CHECK CHECK CONSTRAINT, the three "check" terms have distinct meanings:

• The first WITH CHECK indicates checking existing data in the table.
• The second CHECK CONSTRAINT indicates enabling the check or foreign key constraint.
• The constraint name specifies the particular constraint to operate on.

In practical development, it is recommended to use ADD CONSTRAINT directly without explicitly adding WITH CHECK, unless dealing with special cases like data cleanup. For default constraints, which do not involve data validation, the concise syntax is usually sufficient.

In summary, understanding the differences between WITH CHECK ADD CONSTRAINT and CHECK CONSTRAINT helps in writing more efficient and reliable database scripts, ensuring data integrity and optimizing query performance.