Sql-server – Constraint violation in a trusted constraint

constraintforeign keysql server

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'.

Thanks

Best Answer

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

Related Question