SQL Server constraint is “not trusted”

Question

I recently heard the term “not trusted”:

SQL Server will mark the constraint as “not trusted”.

Does anyone can explain a bit what is the meaning of that? I know the question is a bit vague but I was wondering if this is a Microsoft specific "term" or is also used by other relational databases?

Answer 1

If you disable a constraint and then enable it again without specifying the WITH CHECK option then SQL Server will mark that constraint as "not trusted" since it can no longer rely on the existence of the constraint to guarantee that the data is consistent with the constraint conditions. This results in the constraint being ignored for the purposes of execution plans, so the best practice is to always use WITH CHECK when re-enabling constraints.