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.
- 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.
- 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.
SQL TECH BLOG 
Thanks for reminding me about this. I found 116 FKs in Production that are not trusted, and they should be!
LikeLike
Getting a FK back to trusted status is almost impossible in a 24×7 environment. For SQL Server to go back and check existing data, it puts a schema lock on the child table for the duration of the data check. This is almost unacceptable in a 24×7 operation.
LikeLike
This particular implementation of PK – FK relations is not the only way to do it. I liked the old Watcom SQL Anywhere method. Primary key values appear only in the referenced table. But all the foreign key references are implemented as pointer chains. This makes joins incredibly fast, prevents orphans, etc. This is a version of what we used to do inside network databases, so the technology was very well known before it was used in SQL.
LikeLike
In a testing environment, this will produce the necessary output:
SELECT ‘ALTER TABLE ‘+OBJECT_NAME(fk.parent_object_id)+
‘ WITH CHECK CHECK CONSTRAINT ‘+fk.name+’;’ as Instruction
FROM sys.foreign_keys fk
inner join sys.foreign_key_columns fkc on fk.object_id=fkc.constraint_object_id
WHERE is_not_trusted = 1
LikeLike