HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Running Check Check Constraint and still have untrusted foreign keys (not a replication issue)

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
untrustedissueforeignreplicationkeysrunningconstraintandstillcheck

Problem

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!

Solution

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.

Context

StackExchange Database Administrators Q#180325, answer score: 3

Revisions (0)

No revisions yet.