DaveWentzel.com            All Things Data

Untrusted Constraints

 

When you issue a NOCHECK CONSTRAINT then issue a CHECK CONSTRAINT it is likely your constraint will be labeled as untrusted.  This is because the CHECK CONSTRAINT (re-enabling the constraint) does not actually check existing data, so the database engine views the constraint as untrusted.  This can have ramifications in your query plans. 
 
How do you avoid this?  Ensure you use WITH CHECK CHECK CONSTRAINT instead.  This clause does in fact check existing data. 
 
How can you tell if this might be an issue in your db?
 
select table_name, constraint_name

from information_schema.table_constraints

where (constraint_type = 'FOREIGN KEY' or constraint_type = 'CHECK')

and objectproperty(object_id(constraint_name), 'CnstIsNotTrusted') = 1
 
 

Add new comment