I have a table with a constraint on it that has an existing record that is in violation of that constraint. While this is possible if someone applied the constraint without a check and after violation data was inserted, how would it be possible that the is_not_trusted flag is zero in the sys.foreign_keys table?

This is the situation I currently have, a data violation on a foreign key where the is_not_trusted flag is zero. I thought that was essentially the flag that confirms whether the constraint has been enforced 'religiously'.



1 Answer 1


From comments:

This might happen due to the the value physically written on the page being incorrect (data corruption). Or a bug in the product (probably less likely but possible). Run DBCC CHECKDB to see if that finds any issues. – Martin Smith Jun 18 '16 at 0:07 3

Along Martin's comment - did the data get into the table via MERGE? Here is one bug (closed as "Won't Fix") that can do exactly this, and another bug (also "Won't Fix"). If you need more reasons to avoid MERGE, see this article. – Aaron Bertrand♦ Jun 18 '16 at 3:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.