Re-Trust Untrusted Foreign Keys and Constraints

• shaun.stuart

  SSCertifiable

  Points: 7100

  More actions

  May 31, 2013 at 10:39 am

  #294656

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

• Tony Trus

  Say Hey Kid

  Points: 684

  More actions

  June 17, 2013 at 8:14 am

  #1624607

  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

  More actions

  May 2, 2016 at 12:55 pm

  #1875995

  Thanks for the script.

• adelio.stevanato 21159

  SSC Enthusiast

  Points: 194

  More actions

  June 30, 2017 at 4:18 am

  #1948894

  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?