3

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?

koninos
  • 4,969
  • 5
  • 28
  • 47
  • https://stackoverflow.com/questions/16312866/how-to-make-foreign-key-constraints-trusted#16313703 maybe this helps you – gofr1 Apr 26 '16 at 09:32

2 Answers2

5

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.

Nathan Griffiths
  • 12,277
  • 2
  • 34
  • 51
0

Everytime MSSQL cannot check the constraint:

  1. If you disable a constraint and enable without check:
    • Alter table nocheck constraint constraintname
    • Alter table with nocheck check constraint constraintname
  2. If you create a constraint disables:
    • Alter table with nocheck add constraint ...
  3. If you enable a constraint but there are rows that violates the constraint

Too see rows that violates the constraint you can use DBCC CHECKCONSTRAINTS

Radioleao
  • 394
  • 1
  • 4
  • 19