Click or drag to resize

SA0154 : Constraint is not trusted

The topic describes the SA0154 analysis rule.

Message

Constraint is not trusted

Description

The 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:
SQL
1ALTER TABLE table_name
2      NOCHECK CONSTRAINT constraint_name;
Syntax for re-enabling a constraint:
SQL
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 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 }.

Scope

The rule has a ContextOnly scope and is applied only on current server and database schema.

Parameters

Rule has no parameters.

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Categories

Performance Rules

Additional Information
See Also

Other Resources