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)