SA0154 : Constraint is not trusted |
![]() |
Ensuring constraints are marked as trusted is crucial for optimal query performance.

In SQL Server, constraints play a vital role in maintaining data integrity and assisting the query optimizer in improving performance. However, if constraints become not trusted, the optimizer cannot rely on them, potentially resulting in less efficient query execution plans.
For example:
1-- Query creating an untrusted constraint 2ALTER TABLE SalesOrder 3 NOCHECK CONSTRAINT FK_CustomerID; 4 5-- Re-enabling the constraint without checking the existing data 6ALTER TABLE SalesOrder 7 CHECK CONSTRAINT FK_CustomerID;
In this example, re-enabling a constraint without specifying WITH CHECK leaves it untrusted. This means SQL Server will ignore the constraint during query optimization, which might lead to inefficient query plans.
-
Untrusted constraints are not considered by the SQL Server query optimizer, potentially leading to suboptimal execution plans and slower performance.
-
Neglecting to ensure constraints are trusted can result in inconsistencies, as the assumption of data integrity is compromised.
`

Ensure that constraints are trusted to optimize query performance in SQL Server by re-enabling them with data validation.
Follow these steps to address the issue:
-
Identify the constraints that are currently untrusted using a query to check constraints status:
-
Use the ALTER TABLE syntax to re-enable each untrusted constraint with validation. Replace table_name and constraint_name with the appropriate values:
For example:
1-- Example query to find untrusted constraints 2SELECT name AS ConstraintName, 3 OBJECT_NAME(parent_object_id) AS TableName 4FROM sys.check_constraints 5WHERE is_not_trusted = 1; 6 7-- Correct re-enabling of a constraint 8ALTER TABLE SalesOrder 9 WITH CHECK CHECK CONSTRAINT FK_CustomerID;

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

