none
sys.foreign_keys is_not_trusted problem RRS feed

  • Question

  • Hello Forum,

    I ran into difficult to explain situation with sys.foreign_keys.is_not_trusted value for 1 table.

    3 times with not correct for this case statement: 'alter table drop constraint

    then 'alter table create constraint and alter table check constraint'. To re-check this constraint, 2 'check' words required, scripts used wrongly just 1.

    After 5 weeks, 3rd db was loaded with consolidated script at 1 time. Everything what was released to first 2 db's over the interval of 1 months was ran against 3rd db.

    After sql comparison, 1st and 2nd db were identical and had constraint value sys.foreign_keys.is_not_trusted = 1 which was expected.

    3rd db had sys.foreign_keys.is_not_trusted = 0.

    Starting data for all 3 db's were the same and copied from prod. server.

    Can anybody explain why 3rd had desired but not expected value 0? Does the value of foreign_keys.is_not_trusted have something to do with the interval of time?

    if somebody from Microsoft have any insight explaining this unexpected behavior, it would be great.

    Thank you for your time reading it.


    gene

    Friday, March 7, 2014 1:41 AM

Answers

  • If you drop constraints and re-add them, they will be trusted, unless you explicitly had WITH NOCHECK in the command. It's a different story if you disable a constraint. Then you need the funny WITH CHECK CHECK to enable them so that they are trusted.

    Another way to render your constraints untrusted is to bulk load with BCP or BULK INSERT which by default ignores foreign keys and CHECK constraints.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, March 7, 2014 10:23 PM

All replies

  • using check and no check attributes!

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/05fe45dc-b683-4d6d-b641-07dae397798d/question-on-foreign-key-attribute-isnottrusted?forum=transactsql


    sqldevelop.wordpress.com

    Friday, March 7, 2014 6:43 AM
  • If you drop constraints and re-add them, they will be trusted, unless you explicitly had WITH NOCHECK in the command. It's a different story if you disable a constraint. Then you need the funny WITH CHECK CHECK to enable them so that they are trusted.

    Another way to render your constraints untrusted is to bulk load with BCP or BULK INSERT which by default ignores foreign keys and CHECK constraints.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, March 7, 2014 10:23 PM
  • hello Erland, thank you so much. It does explain why newly created  / re-created constraint end up with trusted and the same constraints which experienced bcp's were untrusted.

    gene

    Saturday, March 8, 2014 5:59 PM
  • Hello Gene,

    you can also use this script to fix it: Repair Not Trusted FK Constraints


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, March 8, 2014 7:35 PM
    Moderator