SQL Server – How to Fix Untrusted Foreign Keys After Running Check Constraint

foreign keysql server

Anyone any ideas on this one? As per Brent Ozar's recommendation (thanks Brent and team!), I've ran the query below which shows FK's that are untrusted:

SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
from sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0;
GO

SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
from sys.check_constraints i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND  i.is_disabled = 0;
GO

It displays two keys. I then run WITH CHECK CHECK CONSTRAINT against these two foreign keys (they are on massive tables), which takes some time but eventually completes. I run the checking query above again, and they are still there!

I've done some research online and found that "is_not_for_replication" can be the common cause for this, but when I run:
SELECT name, is_not_trusted, is_not_for_replication FROM sys.foreign_keys WHERE is_not_trusted = 1; it is showing is_not_for_replication = 0.

Anyone any ideas why these foreign keys aren't trusted despite running CHECK CHECK CONSTRAINT? It should be noted that I am able to run CHECK CHECK CONSTRAINT against other foreign keys successfully.

Thanks in advance!

Best Answer

So, turns out the Application Code uses BULK INSERTS which makes the FK's become un-trusted. To resolve this, I need to get the Developers to add in WITH ( CHECK_CONSTRAINTS ) to the BULK INSERT to resolve the issue and then re-run the WITH CHECK CHECK CONSTRAINTS. Thanks to Dan Guzman for the prompt.

Related Question