SA0154B : Constraint not checked and left not trusted

Ensuring constraints are marked as trusted is crucial for optimal query performance.

Description

In SQL Server, constraints are essential for maintaining data integrity and aiding the optimizer in query performance improvements. The problem arises when constraints are re-enabled without being checked against existing data. This results in constraints becoming “not trusted,” meaning the SQL Server query optimizer will disregard them, potentially leading to less efficient query execution.

For example:

SQL
Copy
1-- Example of problematic constraint handling
2ALTER TABLE Orders 
3NOCHECK CONSTRAINT FK_CustomerID;
4
5ALTER TABLE Orders 
6CHECK CONSTRAINT FK_CustomerID;

In the above scenario, the foreign key constraint on CustomerID is re-enabled without the WITH CHECK option, leaving it “not trusted”. This may cause the query optimizer to ignore this constraint, which can affect performance and data integrity assurance.

  • The query optimizer cannot assume constraints are valid, leading to potentially less efficient query plans.

  • Data integrity issues may go unnoticed if assumptions about data validation are not met.

How to fix

Ensure constraints are trusted to improve query optimizer efficiency and maintain data integrity.

Follow these steps to address the issue:

  1. Identify the constraint that needs to be validated and trusted. You can query system views like sys.check_constraints or sys.foreign_keys to find untrusted constraints. For example, to find all untrusted foreign key constraints:

  2. Use the ALTER TABLE statement with the WITH CHECK CHECK CONSTRAINT syntax to re-enable the constraint and validate existing data. This ensures the constraint is trusted. Replace table_name and constraint_name with your actual table and constraint names.

  3. Validate that the constraint is now trusted by re-querying system views if necessary.

For example:

SQL
Copy
1SELECT name FROM sys.foreign_keys WHERE is_not_trusted = 1;
2-- Re-enable and trust the constraint
3ALTER TABLE Orders WITH CHECK CHECK CONSTRAINT FK_CustomerID;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

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

Effort To Fix
8 minutes per issue.
Categories

Performance Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1CREATE TABLE dbo.Test1
 2  (
 3   KeyColumn int NOT NULL,
 4   Test2KeyColumn int NOT NULL,
 5   CheckColumn int NOT NULL,
 6   LongColumn char(4000) NOT NULL,
 7   CONSTRAINT PK_Test PRIMARY KEY (KeyColumn),
 8   CONSTRAINT CK_Test CHECK (CheckColumn > 0)
 9  );
10
11-- 1. Craete constraint with nocheck ( not trusted)
12ALTER TABLE [dbo].[Test1] WITH NOCHECK 
13ADD CONSTRAINT [FK_Test2_KeyColumn] 
14FOREIGN KEY (Test2KeyColumn) REFERENCES [dbo].[Test2] (KeyColumn)
15
16-- 2. Disable constraint
17ALTER TABLE dbo.Test NOCHECK CONSTRAINT CK_Test;
18
19-- 3. Enable constraint ( not trusted)
20ALTER TABLE dbo.Test CHECK CONSTRAINT CK_Test;
21
22-- 4. Re-enable constraint (trusted)
23ALTER TABLE dbo.Test WITH CHECK CHECK CONSTRAINT CK_Test;
Analysis Results
  Message Line Column
1 SA0154B : The constraint CK_Test is not checked and is left not trusted. 21 38
See Also

Other Resources

Analysis Rules