Foreign Key is enabled , but not trusted

Question

Foreign Key is enabled , but not trusted

SqlServerDBA318

SSCrazy

Points: 2353
More actions
March 3, 2017 at 10:20 am

#322273

Is there any reason to have a foreign key enabled, but not trusted?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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 weird
ALTER TABLE [X12].[EDI820Entity] WITH CHECK CHECK CONSTRAINT [FK__EDI820Entity__EDI820DataID];

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rob Buecker SSChasing Mays Points: 639 More actions · Answer 2

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/

SqlServerDBA318 SSCrazy Points: 2353 More actions · Answer 3

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!

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

butcherking13 - Friday, March 3, 2017 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!

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

Lowell SSC Guru Points: 323518 More actions · Answer 5

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!