SA0154 : Constraint is not trusted |  |
The topic describes the SA0154 analysis rule.
MessageConstraint is not trusted
DescriptionThe rule checks database schema and reports not trusted constraints.
If a constraint is trusted, the optimizer can use it improve query performance.
When a constraint is disabled and later re-enabled, but without checking each row for satisfying the constraint, SQL Server will mark the constraint as 'not trusted'.
Syntax for disabling a constraint:1ALTER TABLE table_name 2 NOCHECK CONSTRAINT constraint_name;
1ALTER TABLE table_name 2 [ WITH { CHECK | NOCHECK } ] 3 CHECK CONSTRAINT constraint_name;
The WITH CHECK option specifies whether the data in the table is to be validated against the re-enabled constraint.
If WITH CHECK is not specified, WITH NOCHECK is assumed as default option for re-enabled constraint and the constraint is marked as 'not trusted'.
 Note |
|---|
The query optimizer does not consider 'not trusted' constraints. Such constraints are ignored until they are re-enabled by using ALTER TABLE table_name WITH CHECK CHECK CONSTRAINT {ALL | constraint_name }. |
ScopeThe rule has a ContextOnly scope and is applied only on current server and database schema.
ParametersRule has no parameters.
RemarksThe rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.
CategoriesPerformance Rules
Additional InformationSee Also