Find and fix untrusted foreign keys

Seems like most of the databases I work on have untrusted foreign keys.  This can happen for a few different reasons that I covered in my post [[Untrusted Constraints]].  I think the biggest reason is that people temporarily disable the FK to do a data load/purge and then forget that the CORRECT syntax for re-enabling the constraint and making it trusted, is a bit goofy.  

WITH CHECK CHECK CONSTRAINT

Yeah, notice CHECK is in there twice, the first as a verb and the second as a noun (yes I know that grammatically those aren't nouns and verbs, the point is one is an object, the other is declaring an action on the object).  

Here is a script that will help you determine if you have untrusted FKs.  It is dynamic SQL and it does NOT actually do any work.  It is "SQL that generates SQL" that you can run at your leisure.  On larger tables you may want to run both the validation script and the WITH CHECK CHECK scripts off hours to avoid concurrency issues, lock escalation, etc.  Might also be worthwhile to index the underlying FK.  (The PK will already be indexed).  

The script can be found in my git repo.  Here's a screenshot of the header.

You have just read [[Find and fix untrusted foreign keys]] on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.