Untrusted Foreign Keys and how to fix them

Claus Munch on Jan 6, 2022 · 1 min read

Untrusted Foreign Keys and how to fix them

Untrusted Foreign Keys are something that is often an overlooked performance issue. 
Let's fix that "easily". 

Symptom:

A untrusted foreign key will force the optimizer to lookup the foreign key, as it cannot trust the other tables integrity. 

Cause:

A foreignkey can become untrusted for several reasons. Most common, from my experience is one of two. Someone disabled the foreign key and enabled it later on, but did not do so with a CHECK of the constraint: 

-- Re-enable the constraint and validate the integrity 
ALTER TABLE dbo.child WITH CHECK CHECK CONSTRAINT parental_advisory; 

Or if some did BULK INSERT of data, without specifying CHECK_CONSTRAINTS, as this will ignore any CHECK or FOREIGN KEY constraints ( [Microsoft Docs] )
Here's a small script, to locate untrusted foreign keys and generate the query to do the data validation. 

SELECT '[' + s.name + '].[' + t.name + '].[' + c.name + ']' AS ForeignKeyConstraint, 'ALTER TABLE [' + t.name + '] WITH CHECK CHECK CONSTRAINT [' + c.name + '];' AS Query from sys.check_constraints c INNER JOIN sys.objects t ON c.parent_object_id = t.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE c.is_not_trusted = 1 AND c.is_not_for_replication = 0 AND c.is_disabled = 0; 
GO 


Word of caution: 

Be aware, that this can be a hefty task, if there is a lot of rows in the table, as it will have to go and check every row in the foreign table.

About the author

Claus Munch has been working with SQL Server in all versions, since 2001. For more than 10 years, he has been running the national SQL Server usergroup. Claus is currently employed by Miracle 42, since 2020.