SA0154B : Constraint not checked and left not trusted

Introduction

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:

-- Example of problematic constraint handling
ALTER TABLE Orders
NOCHECK CONSTRAINT FK_CustomerID;

ALTER TABLE Orders
CHECK 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:

SELECT name FROM sys.foreign_keys WHERE is_not_trusted = 1;
-- Re-enable and trust the constraint
ALTER 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.

Additional Information

Can you trust your constraints?

Example Test SQL

CREATE TABLE dbo.Test1
  (
   KeyColumn int NOT NULL,
   Test2KeyColumn int NOT NULL,
   CheckColumn int NOT NULL,
   LongColumn char(4000) NOT NULL,
   CONSTRAINT PK_Test PRIMARY KEY (KeyColumn),
   CONSTRAINT CK_Test CHECK (CheckColumn > 0)
  );

-- 1. Craete constraint with nocheck ( not trusted)
ALTER TABLE [dbo].[Test1] WITH NOCHECK
ADD CONSTRAINT [FK_Test2_KeyColumn]
FOREIGN KEY (Test2KeyColumn) REFERENCES [dbo].[Test2] (KeyColumn)

-- 2. Disable constraint
ALTER TABLE dbo.Test NOCHECK CONSTRAINT CK_Test;

-- 3. Enable constraint ( not trusted)
ALTER TABLE dbo.Test CHECK CONSTRAINT CK_Test;

-- 4. Re-enable constraint (trusted)
ALTER 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

Analysis Rules