Re-Trust Untrusted Foreign Keys and Constraints

  • shaun.stuart

    SSCertifiable

    Points: 6688

    Comments posted to this topic are about the item Re-Trust Untrusted Foreign Keys and Constraints

  • Tony Trus

    SSChasing Mays

    Points: 654

    Thanks for the nice script. I needed to change collation on a database which involved ripping out every schema bound object and this came in quite handy with retrusting everything. Thankfully I was able to retrust foreign keys and check constraints with 0 errors. My only suggestion I can think of is to make an easier toggle between fk or ck. It could be as simple as changing your select line to include a commented out line like this:

    SELECT '[' + s.name + '].' + '[' + o.name + ']' AS TableName

    ,i.name AS FKName

    FROM sys.foreign_keys i

    -- FROM sys.check_constraints i

    You could go fancier such as setting a flag that allows either option or both but simply having a commented out line for future reference is nice.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

  • adelio.stevanato 21159

    SSC Enthusiast

    Points: 176

    I am using SQL server 2014 and I have a number of tables with foreign keys where they are set as untrusted. NONE of these FK as set as "NOT FOR REPLICATION" and the command seems to run OK.

    I have  tried dropping the FK and re-creating and it creates ok but is always marked as is_not_trusted = 1
    I have even tried creating a new column of the same datatype and creating a new FK (to the same table) and the same thing happens.
    The FK MAster table looks ok.
    this does not happen to ALL FK just a few (13) out many that exist.

    Any ideas?

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

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