patternsqlMinor
Running Check Check Constraint and still have untrusted foreign keys (not a replication issue)
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:
It displays two keys. I then run
I've done some research online and found that "is_not_for_replication" can be the common cause for this, but when I run:
Anyone any ideas why these foreign keys aren't trusted despite running
Thanks in advance!
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;
GOIt 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.