SQL SERVER – Find Untrusted Foreign Key

Today we are going to see the script in which we can find all the untrusted foreign keys in your database. This question recently came up while talking about keys and relationships with my client during the Comprehensive Database Performance Health Check.

It is quite possible that in the past when you are loading lots of data to your tables, you may have disabled your foreign keys. It is also possible that you have created your foreign keys for the future relationship and have created them with the keyword NOCHECK which does not enforce the data integrity of keys.

It is quite possible that you never need to do anything to untrusted foreign keys. However, if you want to create a list of all the keys, here is the script which you can run on your database.

SELECT	s.name AS SchemaName,
		o.name AS TableName,
		f.name AS KeyName
FROM    sys.foreign_keys f
INNER JOIN sys.objects o 
	ON f.parent_object_id = o.object_id
INNER JOIN sys.schemas s 
	ON o.schema_id = s.schema_id
WHERE   f.is_not_trusted = 1
	AND f.is_not_for_replication = 0
	AND f.is_disabled = 0

Well, that’s it. I have seen many real-world databases without trusted foreign keys, even worst without keys at all. The argument I always receive is that the requirement of the business changed and the data architect forgot to add the relationship between keys.

We should all remember that we are using the relational database and the power of the relational database is in the relations between tables which are enforced by keys. In any case, I never express this to my client during SQL Server Performance Health Check as there are always more things to do than the time actually allows us.

You can also reach out to me with your comments on LinkedIn.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Constraint and Keys, SQL Scripts, SQL Server, SQL Server Security
Previous Post
SQL SERVER – Get Column Names
Next Post
Wait Stats for Performance – SQL in Sixty Seconds #157

Related Posts

4 Comments. Leave new

  • Jason Squires
    March 3, 2021 7:18 am

    You will want to fix liine 2 ;P

    Reply
  • Paul Bradshaw
    March 3, 2021 10:53 pm

    I just recently experienced something very odd. I know if you alter a foreign key to be ‘NOCHECK’, and then alter it back with just “CHECK”, the foreign key will apply to all new data, but won’t check existing data. This is when the FK is “untrusted”. Doing a “WITH CHECK CHECK” will check all existing data (may be slow), and will make the FK “trusted”.

    But what I experienced is a table with a foreign key that was TRUSTED, yet trying to alter “WITH CHECK CHECK” returned an error… meaning there were rows that did not have associated data in the other table. How is this possible? It seems like I can’t trust the “is trusted” property of a foreign key.

    Reply

Leave a Reply

Menu
Exit mobile version