dataplatform.blog

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.