March 3, 2017 at 10:20 am
Is there any reason to have a foreign key enabled, but not trusted?
March 3, 2017 at 11:19 am
when the FK was originally created, it would default to checking all the data, so it would be enabled and trusted.
some processes will disable foreign keys as part of large Insert loads to minimize locking, but they might enable the key again, but forget to enable with the CHECK option.
i suspect that's what you are seeing.
it would be best to re-enable the FK with the check option to make the key trusted again.
the syntax looks like this for a FK: the CHECK CHECK is correct, but looks weirdALTER TABLE [X12].[EDI820Entity] WITH CHECK CHECK CONSTRAINT [FK__EDI820Entity__EDI820DataID];
Lowell
March 3, 2017 at 11:23 am
When doing bulk load with a lot of data, i.e. data warehouse, it can be turned off to improve performance.
Couple of write ups on the topic:
http://www.sqlservercentral.com/blogs/waterox-sql/2014/10/17/untrusted-foreign-keys/
https://www.brentozar.com/blitz/foreign-key-trusted/
March 3, 2017 at 11:31 am
Okay and thanks for the responses! I'm in a new gig and came across a Database that has hundreds so it caught me off guard and made me wonder why it would be like this.
Thanks again!
March 3, 2017 at 11:40 am
butcherking13 - Friday, March 3, 2017 11:31 AMOkay and thanks for the responses! I'm in a new gig and came across a Database that has hundreds so it caught me off guard and made me wonder why it would be like this.Thanks again!
Just in case.... if it is CRM then it was common up to a particular version of CRM 2013 - they had been set as "not for replication" which then sets the not trusted.
This is acknowledged by MS as a bug and they will supply a script on demand to the customers that raise a request for it.
On quite large installations this one is a killer for some of their CRM entities
March 3, 2017 at 1:36 pm
here's a script to generate the commands for you;
I keep this handy all the time: SELECT Quotename(s.NAME) + '.' + Quotename(o.NAME) AS QualifiedObject,
s.NAME AS SchemaName,
o.NAME AS ObjectName,
i.NAME AS ConstraintName,
i.[type_desc],
'' AS ConstraintDefinition,
'ALTER TABLE ' + Quotename(s.NAME) + '.'
+ Quotename(o.NAME)
+ ' WITH CHECK CHECK CONSTRAINT '
+ Quotename(i.NAME) + ';' AS Fix,
--'' AS ConstraintTest
'SELECT * FROM ' + Quotename(s.NAME) + '.' + Quotename(o.NAME) + ' WHERE ' + Quotename(Col_name(colz.parent_object_id, colz.parent_column_id)) + ' NOT IN(SELECT ' + Quotename(Col_name(colz.referenced_object_id, colz.referenced_column_id)) + ' FROM ' + Quotename(Object_name(i.referenced_object_id)) +') ' AS ConstraintTest
--,'[' + 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
INNER JOIN sys.foreign_key_columns colz ON i.parent_object_id = colz.parent_object_id AND i.object_id = colz.constraint_object_id
WHERE i.is_not_trusted = 1
AND i.is_not_for_replication = 0
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply