SA0154 : Constraint is not trusted

Constraint is not trusted

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:

1ALTER TABLE table_name
2      NOCHECK CONSTRAINT constraint_name;

Syntax for re-enabling a constraint:

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 }.

Use `ALTER TABLE table_name WITH CHECK CHECK CONSTRAINT constraint_name` syntax to re-enable the constraint with validating the table data.

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

Rule has no parameters.

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

Performance Rules, Bugs

Can you trust your constraints?

Other Resources