SA0154B : Constraint not checked and left not trusted

The topic describes the SA0154B analysis rule.

Message

Constraint not checked and left not trusted

Description

The rule script for DDL statements which create not trusted constraints or enable constraints without specifying WITH CHECK option.

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
Copy
1ALTER TABLE table_name
2      NOCHECK CONSTRAINT constraint_name;

Syntax for re-enabling a constraint:

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

How to fix

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

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   CheckColumn int NOT NULL,
 5   LongColumn char(4000) NOT NULL,
 6   CONSTRAINT PK_Test PRIMARY KEY (KeyColumn),
 7   CONSTRAINT CK_Test CHECK (CheckColumn > 0)
 8  );
 9
10-- 1. craete constraint with nocheck ( not trusted)
11ALTER TABLE [dbo].[Symbols_CategoryValues] WITH NOCHECK ADD CONSTRAINT 
12[FK_Symbols_CategoryValues_Symbols_CategoryItems] 
13FOREIGN KEY ([ItemID], [CategoryID]) 
14REFERENCES [dbo].[Symbols_CategoryItems] ([ItemID], [CategoryID])
15
16-- 2. disable constraint
17ALTER TABLE dbo.Test
18NOCHECK CONSTRAINT CK_Test;
19
20INSERT INTO dbo.Test (KeyColumn, CheckColumn, LongColumn)
21
22SELECT 1, 1, REPLICATE('a', 4000)
23UNION ALL
24SELECT 2, 2, REPLICATE('b', 4000)
25UNION ALL
26SELECT 3, 3, REPLICATE('c', 4000)
27UNION ALL
28SELECT 4, 4, REPLICATE('d', 4000)
29UNION ALL
30SELECT 5, 5, REPLICATE('e', 4000)
31UNION ALL
32SELECT 6, 6, REPLICATE('f', 4000)
33UNION ALL
34SELECT 7, 7, REPLICATE('g', 4000);
35
36
37-- 3. enable constraint ( not trusted)
38ALTER TABLE dbo.Test
39CHECK CONSTRAINT CK_Test;
40
41-- 4. re-enable constraint (trusted)
42ALTER TABLE dbo.Test
43WITH CHECK
44CHECK CONSTRAINT CK_Test;
Analysis Results
  Message Line Column
1 SA0154B : The constraint CK_Test is not checked and is left not trusted. 40 17
See Also

Other Resources

Analysis Rules