Post reply

Untrusted Foreign Key

  • June 13, 2012 at 1:21 pm

    #259762

    I am finding a few foreign keys in my database that are not trusted

    select object_name(parent_obj), name

    from sysobjects where OBJECTPROPERTY([id], 'CnstIsNotTrusted') = 1

    Usually re-checking the data on the FK will fix this issue

    ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyFK

    But this time it is not working. I have even gone so far as to drop the FK completely, re-add it, then re-run the statement above but the FK is still not trusted.

    Is there any other reason which would cause the FK to be not trusted that I need to be aware of?

  • June 13, 2012 at 1:32 pm

    #1500656

    Is the constraint created with "not for replication"? If so, could you try to create it without this?


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • June 13, 2012 at 3:30 pm

    #1500704

    Yes, "not for replication" was included in the generated script. I commented it out and now it is not happening.

    I found this link[/url] with a bit of information on the issue, but I am not entirely clear on why NOT FOR REPLICATION would cause this.

    Anyhow, good to know and thanks for the assistance!

  • June 14, 2012 at 1:48 am

    #1500822

    "Not for replication" on a foreign key instructs SQL Server not to check the constraint when the update to the FK column has come from a replication source.

    As a result, the table could contain data that breaks the FK constraint, therefore it has to be treated as untrusted.

  • June 14, 2012 at 3:54 am

    #1500870

    Here's a link with information about "not for replication": Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply