locked
Constraint violation in a trusted constraint RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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
    Friday, June 17, 2016 10:41 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have seen this happen. I think that DBCC CHECKDB was clean for this database. But we had just restored another database on the same server we had to resort to REPAIR_WITH_ALLOW_DATA_LOSS. The I/O subsystem on that server had gone from bad to worse.

    That is, my prime suspicion is corruption. Run DBCC CHECKDB and DBCC CHECKCONSTRAINTS - the latter will reveal if there any more violations.

    What does sys.databases.page_verify_option_desc report for this database?

    Saturday, June 18, 2016 9:07 AM