Foreign keys are an interesting feature of relational databases. They help enforce data integrity, sometimes help improve performance by eliminating joins, and sometimes slow down DML operations (inserts, updates, and deletes).

For this post I want to focus on what a non-trusted FK or constraint is. By it’s definition, SQL Server does not trust the relationship and this could be both good and bad.

The Good

I don’t need to validate relationships on DML. This can be great for performance.

Inserting large amounts of data into the table? Why not disable the check on your key to speed along the process? The following example shows what happens when a key is trusted and not.

Note that the trusted keys greatly reduce performance for this simple insert statement. Too many keys may also lead to deadlocks if the application isn’t designed well.

The Bad

Keys and constraints provide performance benefits on some SELECT queries and also keep the data in check by validating what is inserted or updated. They can also cascade delete data in setup to do so.

When a constraint or key is not trusted none of the aforementioned items can be performed.

Let’s take a look at how join elimination works for trusted and non-trusted constraints and keys.

Note that the optimizer completely eliminated a branch from the query plan. This is because the data is trusted in the child table and does not need to be accessed to produce the result.

How do I find Non-Trusted Keys in my instance?

Finding the Foreign Keys with these issues is easy. Simply run the following query to identify the offenders. *Note that this should be run in each database on the instance

SELECT OBJECT_NAME(referenced_object_id) as obj, name
FROM sys.foreign_keys
WHERE is_not_trusted = 1

Each row returned is a foreign key that is not trusted.

Why is it untrusted? Perhaps we disabled the check to load data and neglected to re-enable it?

No matter what the reason is the next part is not as simple. This is for two reasons.

1. The data in the child table may not be valid. Since the key was not being checked I may have data in my table that isn’t represented in the parent.
2. The syntax is a bit silly. As Mike Byrd in Austin, TX says, Microsoft studders. The syntax to reenable is “CHECK CHECK”. Let’s look at how we reenable the Address key check.

ALTER TABLE Person.BusinessEntityAddress WITH CHECK CHECK CONSTRAINT FK_BusinessEntityAddress_Address_AddressID;

In summary

Know when Keys are best trusted and not trusted.

Evaluate your keys on a regular basis. What you find may be shocking.

If you’d like to work with me or need help tuning your environment, please reach out on LinkedIn, Twitter, or daniel@austindatapros.com. I’m always happy to meet new friends in the community.